python生成excel文件报扩展名错误_Java使用POI导出Excel .xlsx提示文件格式或文件扩展名有误...

1 packagecom.netmarch.ajgl.common.utils.exportExcel;2

3 import org.apache.poi.ss.usermodel.*;4 importorg.apache.poi.ss.util.CellRangeAddress;5 importorg.apache.poi.ss.util.RegionUtil;6 importorg.apache.poi.xssf.streaming.SXSSFCell;7 importorg.apache.poi.xssf.streaming.SXSSFRow;8 importorg.apache.poi.xssf.streaming.SXSSFSheet;9 importorg.apache.poi.xssf.streaming.SXSSFWorkbook;10 importorg.apache.poi.xssf.usermodel.XSSFCell;11

12 importjava.util.Iterator;13 importjava.util.LinkedHashMap;14 importjava.util.List;15 importjava.util.Map;16

17 /**

18 * @Author: songyaru19 * @Date: 2020/5/14 8:5820 * @Version 1.021 */

22 public classExcelUtils {23

24 /**

25 * 生成表格(用于生成复杂表头)26 *27 *@paramsheetName sheet名称28 *@paramwb 表对象29 *@paramcellListMap 表头数据 {key=cellRowNum-1}30 *@paramcellRowNum 表头总占用行数31 *@paramexportData 行数据32 *@return

33 *@throwsException34 */

35 @SuppressWarnings({"rawtypes", "unchecked"})36 public static SXSSFWorkbook createCSVUtil(String sheetName,String titleName, SXSSFWorkbook wb, Map>cellListMap,37 Integer cellRowNum,Integer columnSize, List exportData,String tjType) throwsException {38 //设置表格名称

39 SXSSFSheet sheet =(SXSSFSheet) wb.createSheet(sheetName);40 sheet.trackAllColumnsForAutoSizing();41 sheet.autoSizeColumn(1, true);42

43 //标题字体

44 Font titleFont =wb.createFont();45 titleFont.setFontName("宋体");46 titleFont.setFontHeightInPoints((short) 18);47 //headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

48 titleFont.setBold(true);//粗体显示

49 titleFont.setColor(IndexedColors.BLACK.getIndex());50

51 //表头

52 Font headerFont =wb.createFont();53 headerFont.setFontName("宋体");54 headerFont.setFontHeightInPoints((short) 14);55 //headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

56 headerFont.setBold(true);//粗体显示

57 headerFont.setColor(IndexedColors.BLACK.getIndex());58

59 //正文字体

60 Font contextFont =wb.createFont();61 contextFont.setFontName("宋体");62 contextFont.setFontHeightInPoints((short) 12);63

64 contextFont.setColor(IndexedColors.BLACK.getIndex());65

66 //标题样式，左右上下居中

67 CellStyle titleStyle =wb.createCellStyle();68 titleStyle.setFont(titleFont);69 titleStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中

70 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

71 titleStyle.setLocked(true);72 titleStyle.setBorderBottom(BorderStyle.THIN); //下边框73

74 //表头样式，左右上下居中

75 CellStyle headerStyle =wb.createCellStyle();76 headerStyle.setFont(headerFont);77 headerStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中

78 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

79 headerStyle.setLocked(true);80 headerStyle.setWrapText(false);//自动换行

81 headerStyle.setBorderBottom(BorderStyle.THIN); //下边框

82 headerStyle.setBorderLeft(BorderStyle.THIN);//左边框

83 headerStyle.setBorderTop(BorderStyle.THIN);//上边框

84 headerStyle.setBorderRight(BorderStyle.THIN);//右边框85

86 //单元格样式，左右上下居中 边框

87 CellStyle commonStyle =wb.createCellStyle();88 commonStyle.setFont(contextFont);89 commonStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中

90 commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

91 commonStyle.setWrapText(false);//自动换行

92 commonStyle.setBorderBottom(BorderStyle.THIN); //下边框

93 commonStyle.setBorderLeft(BorderStyle.THIN);//左边框

94 commonStyle.setBorderTop(BorderStyle.THIN);//上边框

95 commonStyle.setBorderRight(BorderStyle.THIN);//右边框96

97 //单元格样式，竖向 边框

98 CellStyle verticalStyle =wb.createCellStyle();99 verticalStyle.setFont(contextFont);100 verticalStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中

101 verticalStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

102 verticalStyle.setRotation((short) 255);//竖向

103 verticalStyle.setLocked(true);104 verticalStyle.setWrapText(false);//自动换行

105 verticalStyle.setBorderBottom(BorderStyle.THIN); //下边框

106 verticalStyle.setBorderLeft(BorderStyle.THIN);//左边框

107 verticalStyle.setBorderTop(BorderStyle.THIN);//上边框

108 verticalStyle.setBorderRight(BorderStyle.THIN);//右边框109

110

111 //产生表格标题行

112 Row titleRow = sheet.createRow(0);113 Cell cellTiltle = titleRow.createCell(0);114 sheet.setDefaultColumnWidth(15);115 CellRangeAddress titelCellRange=new CellRangeAddress(0, 0, 0, (columnSize - 1));116 sheet.addMergedRegion(titelCellRange);117 RegionUtil.setBorderBottom(BorderStyle.THIN, titelCellRange, sheet);//设置合并单元格的边框

118 /*设置行高*/

119 titleRow.setHeight((short)800);120

121 cellTiltle.setCellStyle(titleStyle);122 cellTiltle.setCellValue(titleName);123

124

125 for (int t = 0; t < cellRowNum; t++) {126 SXSSFRow row = (SXSSFRow) sheet.createRow(t+1);127 List cellNameList =cellListMap.get(String.valueOf(t));128

129 for(CellModel cellModel : cellNameList) {130 if (cellModel.getStartRow() == cellModel.getEndRow() && cellModel.getStartColumn() ==cellModel.getEndColumn()) {131 //【开始行和结束行在同一行】 和 【开始列和结束列在同一列】同时出现的话，那就没有合并单元格的意义了。

132 } else{133 CellRangeAddress cellRangeAddress=newCellRangeAddress(cellModel.getStartRow(),134 cellModel.getEndRow(), cellModel.getStartColumn(), cellModel.getEndColumn());135 sheet.addMergedRegion(cellRangeAddress);136 }137 }138

139 for (int i = 0; i < cellNameList.size(); i++) {140 CellModel cellModel =cellNameList.get(i);141 //遍历插入表头

142 SXSSFCell cell =(SXSSFCell) row.createCell(cellModel.getStartColumn());143 cell.setCellValue(cellModel.getCellName());144 cell.setCellStyle(headerStyle);145 }146 }147 for(LinkedHashMap hashMap : exportData) {148 SXSSFRow rowValue = (SXSSFRow) sheet.createRow(cellRowNum+1);149 Iterator iteratorRow =hashMap.entrySet().iterator();150 while(iteratorRow.hasNext()) {151 Map.Entry entryRow =iteratorRow.next();152 Integer key =Integer.valueOf(entryRow.getKey().toString());153 String value = "";154 if (entryRow.getValue() != null) {155 value =entryRow.getValue().toString();156 } else{157 value = "";158 }159 SXSSFCell cellValue = (SXSSFCell) rowValue.createCell(key - 1);160 cellValue.setCellValue(value);161 cellValue.setCellStyle(commonStyle);162 }163 cellRowNum++;164 }165

166

167 /*设置行宽*/

168 int columnWidth[] = {8000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000};169 for (int i = 0; i < columnSize; i++) {170 sheet.setColumnWidth(i, columnWidth[i]);171 }172 //合并单元框边框有问题，需要在全部单元格合并之后调整边框,否则会覆盖的

173 CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 2, 9, 9);174 RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);//设置合并单元格的右边框

175

176

177

178 /*

179 // 设置为根据内容自动调整列宽 必须在单元格设值以后进行180 for (int k = 0; k < exportData .size(); k++) {181 sheet.autoSizeColumn(k);182 }183 // 处理中文不能自动调整列宽的问题184 setSizeColumn(sheet, exportData.size());*/

185

186

187 returnwb;188 }189

190 //poi设置自适应列宽

191 private static void setSizeColumn(SXSSFSheet sheet, intsize) {192 for (int columnNum = 0; columnNum < size; columnNum++) {193 int columnWidth = sheet.getColumnWidth(columnNum) / 256;194 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {195 SXSSFRow currentRow;196 //当前行未被使用过

197 if (sheet.getRow(rowNum) == null) {198 currentRow =sheet.createRow(rowNum);199 } else{200 currentRow =sheet.getRow(rowNum);201 }202

203 if (currentRow.getCell(columnNum) != null) {204 SXSSFCell currentCell =currentRow.getCell(columnNum);205 if (currentCell.getCellType() ==XSSFCell.CELL_TYPE_STRING) {206 int length =currentCell.getStringCellValue().getBytes().length;207 if (columnWidth

217 }