xlsx转xls（根据模板导出Excel文件(xls和xlsx)）

Apache POI

POI为【Poor Obfuscation Implementation】的首字母缩写，意为【可怜的模糊实现】。Java程序对Microsoft Office格式档案读和写的功能。

POI是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。可以使用Java读取和创建、修改MS Excel文件，还可以使用Java读取和创建MS Word和MSPowerPoint文件，POI提供Java操作Excel解决方案（适用于Excel97-2008）。

Web下载功能的关注点：

具体代码：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.10</version> </dependency>

//============================================================================================// //=====根据模板，使用poi组件导出exeel文件 //============================================================================================// try { ClassPathResource classPathResource = new ClassPathResource("template/费用明细模板20201202.xls"); InputStream input = classPathResource.getInputStream(); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(input); HSSFSheet hssFSheet = hssfWorkbook.getSheetAt(0); this.fillUserBillEntryListForSheet(hssfWorkbook, hssFSheet, userBillEntryList); String fileName = "费用明细_" + DateUtil.Y_M_DAY.format(new Date()); fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls"); response.setContentType("application/octet-stream"); hssfWorkbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } //============================================================================================// //=====The end //============================================================================================//

读取模板（.xls）设置样式并输出

package com.what21.poi; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import java.io.*; public class PoiExcel2XlsTest { public static void main(String[] args) throws IOException { String fileStr = "d://费用明细模板20201202.xls"; InputStream input = new FileInputStream(fileStr); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(input); HSSFSheet hssFSheet = hssfWorkbook.getSheetAt(0); //设置第3行 HSSFRow row3 = hssFSheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellStyle(getCellStyle(hssfWorkbook)); cell31.setCellValue("11111111"); HSSFCell cell32 = row3.createCell(1); cell32.setCellStyle(getCellStyle(hssfWorkbook)); cell32.setCellValue("22222222"); HSSFCell cell33 = row3.createCell(2); cell33.setCellStyle(getCellStyle(hssfWorkbook)); cell33.setCellValue("33333333"); HSSFCell cell34 = row3.createCell(3); cell34.setCellStyle(getCellStyle(hssfWorkbook)); cell34.setCellValue("44444444"); HSSFCell cell35 = row3.createCell(4); cell35.setCellStyle(getCellStyle(hssfWorkbook)); cell35.setCellValue("55555555"); //设置第4行 HSSFRow row4 = hssFSheet.createRow(3); row4.createCell(0).setCellValue("11111111"); row4.createCell(1).setCellValue("22222222"); row4.createCell(2).setCellValue("33333333"); row4.createCell(3).setCellValue("44444444"); row4.createCell(4).setCellValue("55555555"); String exportFileStr = "d://费用明细模板20201202.1.xls"; hssfWorkbook.write(new FileOutputStream(new File(exportFileStr))); } /** * @param hssfWorkbook * @return */ public static HSSFCellStyle getCellStyle(HSSFWorkbook hssfWorkbook) { // 样式设置 HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); // =======================================================================// cellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框 cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框 cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框 cellStyle.setBorderTop(BorderStyle.THIN);//设置上边框 // =======================================================================// // 设置边框颜色 cellStyle.setBottomBorderColor((short) 1); // 设置每个单元格的文字居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // =======================================================================// //通过HSSFWorkbook创建一个HSSFFont HSSFFont font = hssfWorkbook.createFont(); //设置一个字体的颜色 //2表示红色 //3表示绿色 //4表示蓝色 //5表示黄色 //6表示紫色 //7表示亮蓝色 //8表示黑色 //9表示白色 font.setColor((short) 8); //设置字体的风格：仿宋_GB2312、黑体 font.setFontName("黑体"); //设置一个字体的大小,此数值的取值与Excel中的字体大小取值一样 font.setFontHeightInPoints((short) 9); //粗体显示 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //把该字体应用到当前的样式 cellStyle.setFont(font); // =======================================================================// // 设置自动换行 cellStyle.setWrapText(true); // =======================================================================// // 设置背景色 cellStyle.setFillForegroundColor((short) 7); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // =======================================================================// return cellStyle; } }

读取模板（.xlsx）设置样式并输出

package com.what21.poi; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.xssf.usermodel.*; import java.io.*; public class PoiExcel2XlsxTest { public static void main(String[] args) throws IOException { String fileStr = "d://费用明细模板20201202.xlsx"; InputStream input = new FileInputStream(fileStr); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(input); XSSFSheet xssFSheet = xssfWorkbook.getSheetAt(0); //设置第3行 XSSFRow row3 = xssFSheet.getRow(2); XSSFCell cell31 = row3.createCell(0); cell31.setCellStyle(getCellStyle(xssfWorkbook)); cell31.setCellValue("11111111"); XSSFCell cell32 = row3.createCell(1); cell32.setCellStyle(getCellStyle(xssfWorkbook)); cell32.setCellValue("22222222"); XSSFCell cell33 = row3.createCell(2); cell33.setCellStyle(getCellStyle(xssfWorkbook)); cell33.setCellValue("33333333"); XSSFCell cell34 = row3.createCell(3); cell34.setCellStyle(getCellStyle(xssfWorkbook)); cell34.setCellValue("44444444"); XSSFCell cell35 = row3.createCell(4); cell35.setCellStyle(getCellStyle(xssfWorkbook)); cell35.setCellValue("55555555"); //设置第4行 XSSFRow row4 = xssFSheet.getRow(3); row4.createCell(0).setCellValue("11111111"); row4.createCell(1).setCellValue("22222222"); row4.createCell(2).setCellValue("33333333"); row4.createCell(3).setCellValue("44444444"); row4.createCell(4).setCellValue("55555555"); String exportFileStr = "d://费用明细模板20201202.1.xlsx"; xssfWorkbook.write(new FileOutputStream(new File(exportFileStr))); } /** * @param xssFWorkbook * @return */ public static XSSFCellStyle getCellStyle(XSSFWorkbook xssFWorkbook) { // 样式设置 XSSFCellStyle cellStyle = xssFWorkbook.createCellStyle(); // =======================================================================// cellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框 cellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框 cellStyle.setBorderRight(BorderStyle.THIN);//设置右边框 cellStyle.setBorderTop(BorderStyle.THIN);//设置上边框 // =======================================================================// // 设置边框颜色 cellStyle.setBottomBorderColor((short) 1); // 设置每个单元格的文字居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // =======================================================================// //通过XSSFWorkbook创建一个HSSFFont XSSFFont font = xssFWorkbook.createFont(); //设置一个字体的颜色 //2表示红色 //3表示绿色 //4表示蓝色 //5表示黄色 //6表示紫色 //7表示亮蓝色 //8表示黑色 //9表示白色 font.setColor((short) 8); //设置字体的风格：仿宋_GB2312、黑体 font.setFontName("黑体"); //设置一个字体的大小,此数值的取值与Excel中的字体大小取值一样 font.setFontHeightInPoints((short) 9); //粗体显示 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //把该字体应用到当前的样式 cellStyle.setFont(font); // =======================================================================// // 设置自动换行 cellStyle.setWrapText(true); // =======================================================================// // 设置背景色 cellStyle.setFillForegroundColor((short) 7); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // =======================================================================// return cellStyle; } }