当前位置: 代码迷 >> 综合 >> Excel poi4.0导出工具类 兼容.xls和.xlsx
  详细解决方案

Excel poi4.0导出工具类 兼容.xls和.xlsx

热度:64   发布时间:2023-11-17 12:57:40.0

参数解释: 

String sheetName = "前台导入错误提示";
String[] headers = {"错误序号", "错误信息"};
String[] columns = {"xuhao", "info"};
originalFilename =======》例:xxxx.xls

工具类: 


/*** @author: lucifer* @description: Excel导出工具类*/
@Slf4j
public class ExportExcelUtil<T> {/*** 兼容.xls和.xlsx格式** @param originalFilename* @return*/private Workbook getWorkbook(String originalFilename) {Workbook workbook = null;if (originalFilename.endsWith(EXTENSION_XLS)) {workbook = new HSSFWorkbook();} else if (originalFilename.endsWith(EXTENSION_XLSX)) {workbook = new XSSFWorkbook();}return workbook;}/*** 导出** @param originalFilename* @param sheetName* @param headers* @param columns* @param lists* @throws Exception*/public void export(String originalFilename, String sheetName, String[] headers, String[] columns, List<T> lists) throws Exception {Workbook workbook = getWorkbook(originalFilename);Sheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(15);//设置表头样式CellStyle style = setHeaderStyle(workbook);Row row = sheet.createRow(0);for (int i = 0; i < headers.length; i++) {Cell headerCell = row.createCell(i);headerCell.setCellValue(headers[i]);headerCell.setCellStyle(style);}Iterator<T> it = lists.iterator();int rowIndex = 0;while (it.hasNext()) {rowIndex++;row = sheet.createRow(rowIndex);T t = it.next();Field[] fields = t.getClass().getDeclaredFields();for (int i = 0; i < fields.length; i++) {Field field = fields[i];String fieldName = field.getName();for (int j = 0; j < columns.length; j++) {if (fieldName.equals(columns[j])) {String getMethodName = "get" +fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);Class cls = t.getClass();Method getMethod = cls.getMethod(getMethodName, new Class[]{});Object val = getMethod.invoke(t, new Object[]{});String textVal;if (null != val) {textVal = val.toString();} else {textVal = null;}Cell cell = row.createCell(j);cell.setCellValue(textVal);CellStyle cellStyle = setCellStyle(workbook, i);cell.setCellStyle(cellStyle);//单元格列宽设置sheet.autoSizeColumn(j);sheet.setColumnWidth(j, sheet.getColumnWidth(j)*17/10 > 255 * 256 ? 255 * 256 : sheet.getColumnWidth(j)*17/10);log.info("======================:"+sheet.getColumnWidth(i));log.info("textVal.getBytes().length:" + textVal.getBytes().length);log.info("i:===============" + i + ",j:================" + j + ",textVal:" + textVal);}}}}String filename = sheetName + System.currentTimeMillis() + ".xlsx";//判断是否有文件夹,没有就创建File file = new File(BaseConstant.EXCEL_PATH);if (!file.exists()) {file.mkdirs();}String filepath = BaseConstant.EXCEL_PATH + "\\" + filename;System.out.println("filepath:" + filepath);FileOutputStream out = new FileOutputStream(filepath);workbook.write(out);out.close();// downloadExcel(filepath, response);}/*** 设置表头样式** @param workbook* @return*/private CellStyle setHeaderStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置边框cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);//设置字体Font cellFont = workbook.createFont();cellFont.setBold(true);cellStyle.setFont(cellFont);return cellStyle;}/*** 设置单元格样式*/private CellStyle setCellStyle(Workbook workbook, int i) {CellStyle cellStyle = workbook.createCellStyle();//奇数列 左对齐if ((i & 1) != 1) {cellStyle.setAlignment(HorizontalAlignment.LEFT);} else {//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);}//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置边框cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);//设置字体Font cellFont = workbook.createFont();cellFont.setFontName("仿宋_GB2312");cellStyle.setFont(cellFont);return cellStyle;}/*** 下载*/public static void downloadExcel(String filepath, HttpServletResponse response)throws IOException {File file = new File(filepath);String fileName = file.getName();response.setContentType("application/vnd.ms-excel;charset=utf-8");response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));response.setCharacterEncoding("utf-8");InputStream fis = new BufferedInputStream(new FileInputStream(file));byte[] b = new byte[fis.available()];fis.read(b);response.getOutputStream().write(b);fis.close();}}

BaseConstant:常量类:

    /*** excel ./xls后缀*/public static final String EXTENSION_XLS = ".xls";/*** excel ./xlsx后缀*/public static final String EXTENSION_XLSX = ".xlsx";

pom.xml: 

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.0</version></dependency>

 

 导出模板:

 

  相关解决方案