1.导包
<!-- 读取xml文件用 https://mvnrepository.com/artifact/org.dom4j/dom4j --> <dependency> <groupId>org.dom4j</groupId> <artifactId>dom4j</artifactId> <version>2.1.3</version> </dependency> <!-- 日期格式化工具https://mvnrepository.com/artifact/joda-time/joda-time --> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.12</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans --> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>5.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency>
2.工具类
import com.dlb.pojo.Tab_car; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.joda.time.DateTime; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; public class ExcelPoi { /** * HSSF : 读写 Microsoft Excel XLS 格式文档 * * XSSF : 读写 Microsoft Excel OOXML XLSX 格式文档 * * SXSSF : 读写 Microsoft Excel OOXML XLSX 格式文档 * * HWPF : 读写 Microsoft Word DOC 格式文档 * * HSLF : 读写 Microsoft PowerPoint 格式文档 * * HDGF : 读 Microsoft Visio 格式文档 * * HPBF : 读 Microsoft Publisher 格式文档 * * HSMF : 读 Microsoft Outlook 格式文档 * @param file 上传文件源 * @param filePath 存放路径 */ public void readExcel(MultipartFile file, String filePath) { FileOutputStream fos =null; InputStream is = null; Workbook workbook = null; String filename = file.getOriginalFilename();//文件名 try { is = file.getInputStream();//获取流 //根据文件后缀名判断创建什么工作簿 assert filename != null; String substring = filename.substring(filename.lastIndexOf(".") + 1); System.out.println("substring = " + substring); if ("xls".equals(substring)){ workbook = new HSSFWorkbook(is); }else if ("xlsx".equals(substring)){ workbook = new XSSFWorkbook(is); } assert workbook != null; boolean res = readExcel(is,workbook);//调用读方法 if (res){ fos = new FileOutputStream(new File(filePath+filename)); workbook.write(fos);//生成文件，放入指定路径 } } catch (Exception e){ e.printStackTrace(); } finally { try { if (fos != null){ fos.close(); } } catch (IOException e) { e.printStackTrace(); } try { if (is != null){ is.close(); } } catch (IOException e) { e.printStackTrace(); } } } //读并打印 public boolean readExcel(InputStream is,Workbook workbook){ Sheet sheet =null; Row row =null; Cell cell =null; int a = 0;//遍历完一个表就＋1，如果和表数量一致就是遍历完成 int sheets = workbook.getNumberOfSheets();//一共有几个表 for (int i = 0; i < sheets; i++) { sheet = workbook.getSheetAt(i); row = sheet.getRow(i); if (row!=null){ //第一行一共有几个单元格 int title = row.getPhysicalNumberOfCells(); for (int j = 0; j < title; j++) { cell = row.getCell(j); if(cell!=null){ CellType cellType = cell.getCellType(); //System.out.println(cellType); String stringCellValue = cell.getStringCellValue(); System.out.print(stringCellValue+" | "); } } } System.out.println(); //获取多少行 int numberOfRows = sheet.getPhysicalNumberOfRows(); for (int k = 1; k < numberOfRows; k++) { Row row1 = sheet.getRow(k); if (row1!=null){ int cells = row1.getPhysicalNumberOfCells(); for (int m = 0; m < cells; m++) { cell = row1.getCell(m); if (cell!=null){ CellType cellType = cell.getCellType(); String cv=""; /** * _NONE(-1), * NUMERIC(0), * STRING(1), * FORMULA(2), * BLANK(3), * BOOLEAN(4), * ERROR(5); */ switch (cellType){ case _NONE://空 break; case NUMERIC://日期 普通数字 if (DateUtil.isCellDateFormatted(cell)){ Date date = cell.getDateCellValue(); cv = new DateTime(date).toString(); System.out.println("[日期]"); }else {//转换为字符串输出 // 先获取数据 double cellValue = cell.getNumericCellValue(); //直接强转String类型会出现数字编程科学计数法问题 cv=new BigDecimal(String.valueOf(cellValue)) .stripTrailingZeros()//去除末尾的0 .toPlainString();//输出时不用科学计数法 System.out.print("[数字]"); } break; case STRING://字符串 cv=cell.getStringCellValue(); break; case BLANK: break; case ERROR://数据类型错误 System.out.println("数据错误"); break; case BOOLEAN://布尔 cv=String.valueOf(cell.getBooleanCellValue()); break; case FORMULA://公式 //拿到计算公式 FormulaEvaluator fe = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); String cellFormula = cell.getCellFormula(); //内容值:SUM(D2:D3) System.out.println("内容值:"+cellFormula); //计算 CellValue evaluate = fe.evaluate(cell); String s = evaluate.toString(); //org.apache.poi.ss.usermodel.CellValue [25.0] System.out.println(s); break; } System.out.print(cv+" | "); } } System.out.println(); } } System.err.println("表"+i+"完:"+sheet.getSheetName()); a++; } return a == sheets; } //网页上面下载Excel文件 public void writeExcel(String sheetName,//sheet名字 List<Tab_car> bodyList,//要输出的对象集合 HttpServletResponse response){//服务响应 //创建工作簿 Workbook workbook = new SXSSFWorkbook(); //创建一个sheet表 Sheet sheet = workbook.createSheet(sheetName); //写数据 // 创建一行 Row row = sheet.createRow(0); row.createCell(0).setCellValue("品牌"); row.createCell(1).setCellValue("车牌号"); row.createCell(2).setCellValue("容量座位"); row.createCell(3).setCellValue("颜色"); row.createCell(4).setCellValue("价格"); row.createCell(5).setCellValue("时间"); row.createCell(6).setCellValue("状态"); // 定义样式 CellStyle cellStyle = workbook.createCellStyle(); //内容 for (int i = 0; i < bodyList.size(); i++) { Row row1 = sheet.createRow(i+1); Tab_car tab_car = bodyList.get(i); row1.createCell(0).setCellValue(tab_car.getCarLogo()); row1.createCell(1).setCellValue(tab_car.getCarNumber()); row1.createCell(2).setCellValue(tab_car.getCarZuo()); row1.createCell(3).setCellValue(tab_car.getCarColor()); row1.createCell(4).setCellValue(tab_car.getCarPrice().toString()); String time = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒").format(tab_car.getCarTime()); row1.createCell(5).setCellValue(time); row1.createCell(6).setCellValue(tab_car.getCarStatus()); } //模拟文件，myfile.txt为需要下载的文件 //String path = "E:\\"+fileName+".xlsx"; // new File(path) // FileOutputStream fos = new FileOutputStream(path); // workbook.write(fos); ServletOutputStream outputStream = null; try{ //响应防止乱码 response.setContentType("multipart/form-data"); // 传递中文参数编码 String codedFileName = java.net.URLEncoder.encode("汽车信息","UTF-8"); response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx"); outputStream = response.getOutputStream(); workbook.write(outputStream); }catch (Exception e){ e.printStackTrace(); }finally { if (outputStream!=null){ try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } } }
3.服务使用
//下载 @RequestMapping("/downloadCarIno") @ResponseBody public void dci(HttpServletResponse response){
//从数据库中查出的数据 List<Tab_car> carList = carService.selectAll(); new ExcelPoi().writeExcel("车辆信息",carList,response); }