java生成Excel表格并导出

表格并导出"/>

java生成Excel表格并导出

1.导入相关的依赖

 <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency>

2.创建ExportExcel类

package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Export;import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.stereotype.Controller;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import java.io.IOException;
import java.io.OutputStream;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.RequestMapping;import static com.sun.xml.internal.ws.spi.db.BindingContextFactory.LOGGER;@Controller
@Api(description = "导出Excel")
public class ExportExcel {/*** 导出模板** @param response* @param request* @throws IOException*/@RequestMapping(value = "/download", produces = "text/html;charset=UTF-8")public void download(HttpServletResponse response, HttpServletRequest request) {//创建HSSFWorkbook对象try (HSSFWorkbook wb = new HSSFWorkbook()) {LOGGER.info("进入导出模板方法");//创建HSSFSheet对象HSSFSheet sheet = wb.createSheet("导出模板");//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个HSSFRow row1 = sheet.createRow(0);//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个HSSFCell cell = row1.createCell(0);//合并单元格sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));//向合并单元格中输入字段cell.setCellValue("第一小区户口");//在sheet里创建第二行(下标为1)HSSFRow row2 = sheet.createRow(1);//创建单元格并设置单元格内容row2.createCell(0).setCellValue("ID");row2.createCell(1).setCellValue("户号");row2.createCell(2).setCellValue("与户主关系");row2.createCell(3).setCellValue("姓名");row2.createCell(4).setCellValue("性别");row2.createCell(5).setCellValue("年龄");row2.createCell(6).setCellValue("出生年月");row2.createCell(7).setCellValue("身份证");row2.createCell(8).setCellValue("手机号");row2.createCell(9).setCellValue("民族");row2.createCell(10).setCellValue("派出所");row2.createCell(11).setCellValue("村居委会");//输出Excel文件OutputStream output = response.getOutputStream();response.reset();//filename=后面跟文件名response.setHeader("Content-disposition", "attachment; filename=Community.xlsx");response.setContentType("application/vnd.ms-excel;charset=utf-8");LOGGER.info("即将输出文件流");wb.write(output);output.flush();output.close();} catch (Exception e) {
//            LOGGER.error("导出统计模板生成excel异常", e);} finally {Object RequestContextLocal = null;
//            RequestContextLocal.bindUserOperation("下载模板", "客户支付流程跟踪导入和统计菜单");}LOGGER.info("方法运行结束");}}

3.工具类ExcelUtils

package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Utils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.enity.Community;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;public class ExcelUtils {//总行数private static int totalRows = 12;//总条数private static int totalCells = 12;//错误信息接收器private static String errorMsg;/*** 验证EXCEL文件** @param filePath* @return*/public static boolean validateExcel(String filePath) {if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {errorMsg = "文件名不是excel格式";return false;}return true;}/*excel2003版本的excel文件是.xls格式，excel2007及以上版本的excel的是.xlsx格式。*/// @描述：是否是2003的excel，返回true是2003public static boolean isExcel2003(String filePath)  {return filePath.matches("^.+\\.(?i)(xls)$");}//@描述：是否是2007的excel，返回true是2007public static boolean isExcel2007(String filePath)  {return filePath.matches("^.+\\.(?i)(xlsx)$");}/*** 读取Excel里面客户的信息* @param wb* @return*/private static List<Community> readExcelValue(Workbook wb) {//默认会跳过第一行标题// 得到第一个shellSheet sheet = wb.getSheetAt(0);// 得到Excel的行数totalRows = sheet.getPhysicalNumberOfRows();// 得到Excel的列数(前提是有行数)if (totalRows > 1 && sheet.getRow(0) != null) {totalCells = sheet.getRow(0).getPhysicalNumberOfCells();}List<Community> manychoiceList = new ArrayList<Community>();// 循环Excel行数for (int r = 1; r < totalRows; r++) {Row row = sheet.getRow(r);if (row == null) {continue;}Community community = new Community();// 循环Excel的列for (int c = 0; c < totalCells ; c++) {Cell cell = row.getCell(c);if (null != cell) {if (c == 0) {            //第一列//如果是纯数字,将单元格类型转为Stringif (cell.getCellTypeEnum() == CellType.NUMERIC) {double numericCellValue = cell.getNumericCellValue();community.setID((int)numericCellValue);}} else if (c == 1) {if (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.STRING);}community.setNumber(cell.getStringCellValue());} else if (c == 2) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setRelationship(stringCellValue);}} else if (c == 3) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setName(stringCellValue);}} else if (c == 4) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setGender(stringCellValue);}} else if (c == 5) {if (cell.getCellTypeEnum() == CellType.STRING) {
//                            double numericCellValue = cell.getNumericCellValue();
//                            community.setAge(String.valueOf((int)numericCellValue));String  numericCellValue = cell.getStringCellValue();community.setAge(numericCellValue);}if (cell.getCellTypeEnum() == CellType.NUMERIC) {
//                            double numericCellValue = cell.getNumericCellValue();
//                            community.setAge(String.valueOf((int)numericCellValue));double  numericCellValue = cell.getNumericCellValue();community.setAge(String.valueOf((int)numericCellValue));}if (cell.getCellTypeEnum() == CellType.FORMULA) {
//                            double numericCellValue = cell.getNumericCellValue();
//                            community.setAge(String.valueOf((int)numericCellValue));double  numericCellValue = cell.getNumericCellValue();community.setAge(String.valueOf((int)numericCellValue));}} else if (c == 6) {if (cell.getCellTypeEnum() == CellType.STRING) {String  numericCellValue = cell.getStringCellValue();community.setCard(numericCellValue);}} else if (c == 7) {if (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.STRING);} String numericCellValue = cell.getStringCellValue();community.setDate(numericCellValue);} else if (c == 8) {if (cell.getCellTypeEnum() == CellType.NUMERIC) {cell.setCellType(CellType.STRING);}String numericCellValue = cell.getStringCellValue();community.setMobile(numericCellValue);} else if (c == 9) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setNationality(stringCellValue);}} else if (c == 10) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setStation(stringCellValue);}} else if (c == 11) {if (cell.getCellTypeEnum() == CellType.STRING) {String stringCellValue = cell.getStringCellValue();community.setCommittee(stringCellValue);}//score属性是int数据类型，转换成int
//                        manychoice.setScore(Integer.valueOf(cell.getStringCellValue()));}}}//将excel解析出来的数据赋值给对象添加到list中manychoiceList.add(community);}return manychoiceList;}/*** 根据excel里面的内容读取客户信息* @param is 输入流* @param isExcel2003 excel是2003还是2007版本* @return* @throws IOException*/public static List<Community> createExcel(InputStream is, boolean isExcel2003) {try{Workbook wb = null;if (isExcel2003) {// 当excel是2003时,创建excel2003wb = new HSSFWorkbook(is);} else {// 当excel是2007时,创建excel2007wb = new XSSFWorkbook(is);}// 读取Excel里面客户的信息List<Community> communityList = readExcelValue(wb);return communityList;} catch (IOException e) {e.printStackTrace();}return null;}/*** 读EXCEL文件，获取信息集合* @return*/public static List<Community> getExcelInfo(MultipartFile file) {String files = file.getOriginalFilename();//获取文件名try {if (!validateExcel(files)) {// 验证文件名是否合格return null;}boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本if (isExcel2007(files)) {isExcel2003 = false;}List<Community> communityList = createExcel(file.getInputStream(), isExcel2003);return communityList;} catch (Exception e) {e.printStackTrace();}return null;}}

 4.实体类

package cn.java999.uploadandparseexcel.entity;import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;@Data
@AllArgsConstructor
@NoArgsConstructor
@Component
public class Community {@TableIdprivate Integer ID;private String number;private String relationship;private String name;private String gender;private String age;private String Date;private String card;private String Mobile;private String nationality;private String station;private String committee;}

5.mapper

package cn.java999.uploadandparseexcel.mapper;import cn.java999.uploadandparseexcel.entity.Community;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface Communitymapper extends BaseMapper<Community> {}

6.导出流程

 7.开始导出下载

 8.下载完成

 9.导出效果

 总结：到这里我们用java生成Excel表格并导出就完成了，请各位老铁浏览时点个赞并收藏以免用到时找不到，如有不足的地方请评论在下方