java生成复杂excel文件

1、先去 官网下载相关jar包入下：

2、代码实现：public void fun() throws IOException { //文件路径 String fileP锾攒揉敫ath = "C:\\Users\\Administrator\\Desktop\\file\\sample.xls"; //创建Excel文件(Workbook) //HSSFWorkbook workbook = new HSSFWorkbook(); //创建工作表(Sheet) // 声明String数组，并初始化元素（表头名称） //第一行表头字段，合并单元格时字段跨几列就将该字段重复几次 String[] excelHeader0 = {"备注：", " ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "}; // “0,2,0,0” ===> “起始行，截止行，起始列，截止列” String[] headnum0 = {"0,0,1,16"}; //第二行表头字段，其中的空的双引号是为了补全表格边框 String[] excelHeader1 = {"本周卫生负责人：", "", "", "","本月操作：", "", "", "", "","负责人：", "", "", "", "本月黑板报负责人:", "", "", ""}; // 合并单元格 String[] headnum1 = {"1,1,1,3", "1,1,5,8", "1,1,11,12", "1,1,14,16"}; //第三行表头字段 String[] excelHeader2 = {"疾病查房：", "", "", "", "主查者：", "", "", "", "应急演练：", "","", " ", " ", " ", "", " ", " "}; String[] headnum2 = {"2,2,1,3", "2,2,5,7", "2,2,9,16"}; String[] excelHeader3 = {"信息稿：", " ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "}; String[] headnum3 = {"3,3,1,16"}; String[] excelHeader4 = {"本月业务学习：", "", "", "", "内容：", "", "", "", "主讲者：", "","", " ", "形式： ", " ", "", " ", ""}; String[] headnum4 = {"4,4,1,3", "4,4,5,7", "4,4,9,11", "4,4,13,16"}; String[] excelHeader5 = {"第一周工作安排：", " ", " ", "第二周工作安排:", "", " ", "第三周工作按排:", " ", "", "第四周工作安排:"," ", " ", " 第五周工作安排:", " ", " ", "", ""}; String[] headnum5 = {"5,5,1,2", "5,5,4,5", "5,5,7,8", "5,5,10,11", "5,5,13,16"}; String[] excelHeader6 = {"备忘", " ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "," ", " ", " ", " "}; String[] headnum6 = {"6,6,1,16"}; // 声明一个工作簿 HSSFWorkbook wb = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = wb.createSheet(); //创建工作表(Sheet) sheet = wb.createSheet("Test"); // 生成一种样式 HSSFCellStyle style = wb.createCellStyle(); // 设置样式 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一种字体 HSSFFont font = wb.createFont(); // 设置字体 font.setFontName("微软雅黑"); // 设置字体大小 font.setFontHeightInPoints((short) 12); // 字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 在样式中引用这种字体 style.setFont(font); // 生成表格的第一行 // 第一行表头 HSSFRow row = sheet.createRow(0); for (int i = 0; i < excelHeader0.length; i++) { sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度 HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader0[i]); cell.setCellStyle(style); // System.out.println(excelHeader0[i]); if (i >= 0 && i <= 16) { for (int j = 1; j < excelHeader0.length; j++) { // 从第j列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader0[j]); cell.setCellStyle(style); } } row.setHeight((short)512); // 设置列宽 sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum0.length; i++) { sheet.autoSizeColumn(i, true); String[] temp = headnum0[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } // 第二行表头 row = sheet.createRow(1); for (int i = 0; i < excelHeader1.length; i++) { // 自动调整宽度 sheet.autoSizeColumn(i, true); HSSFCell cell = row.createCell(i + 1); cell.setCellValue(excelHeader1[i]); cell.setCellStyle(style); row.setHeight((short)512); if (i >= 0 && i <= 16) { for (int j = 0; j < excelHeader1.length; j++) { // 从第j+1列开始填充 cell = row.createCell(j ); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader1[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum1.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum1[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } // 第三行表头 row = sheet.createRow(2); for (int i = 0; i < excelHeader2.length; i++) { HSSFCell cell = row.createCell(i + 2); cell.setCellValue(excelHeader2[i]); cell.setCellStyle(style); // System.out.println(excelHeader2[i]); // sheet.autoSizeColumn(i, true);// 自动调整宽度 row.setHeight((short)512); if (i >= 0 && i <= 16) { for (int j = 0; j < excelHeader2.length; j++) { // 从第j+2列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader2[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum2.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum2[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(3); for (int i = 0; i < excelHeader3.length; i++) { HSSFCell cell = row.createCell(i + 2); cell.setCellValue(excelHeader3[i]); cell.setCellStyle(style); // System.out.println(excelHea;// 自动调整宽度 row.setHeight((short)512); if (i >=0 && i <= 16) { for (int j = 0; j < excelHeader3.length; j++) { // 从第j+2列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader3[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum3.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum3[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(4); for (int i = 0; i < excelHeader4.length; i++) { HSSFCell cell = row.createCell(i + 2); cell.setCellValue(excelHeader4[i]); cell.setCellStyle(style); // System.out.println(excelHeader2[i]); // sheet.autoSizeColumn(i, true);// 自动调整宽度 row.setHeight((short)512); if (i >= 0 && i <= 16) { for (int j = 0; j < excelHeader4.length; j++) { // 从第j+2列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader4[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum4.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum4[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(5); for (int i = 0; i < excelHeader5.length; i++) { HSSFCell cell = row.createCell(i + 2); cell.setCellValue(excelHeader5[i]); cell.setCellStyle(style); // System.out.println(excelHeader2[i]); // sheet.autoSizeColumn(i, true);// 自动调整宽度 row.setHeight((short)1500); if (i >= 0 && i <= 16) { for (int j = 0; j < excelHeader5.length; j++) { // 从第j+2列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader5[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum5.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum5[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(6); for (int i = 0; i < excelHeader6.length; i++) { HSSFCell cell = row.createCell(i + 2); cell.setCellValue(excelHeader6[i]); cell.setCellStyle(style); // System.out.println(excelHeader2[i]); // sheet.autoSizeColumn(i, true);// 自动调整宽度 row.setHeight((short)512); if (i >= 0 && i <= 16) { for (int j = 0; j < excelHeader6.length; j++) { // 从第j+2列开始填充 cell = row.createCell(j); // 填充excelHeader1[j]第j个元素 cell.setCellValue(excelHeader6[j]); cell.setCellStyle(style); } } sheet.setColumnWidth(0, 20*256); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 4500); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 3000); sheet.setColumnWidth(12, 4500); sheet.setColumnWidth(13, 4600); sheet.setColumnWidth(14, 3000); sheet.setColumnWidth(15, 3000); sheet.setColumnWidth(16, 3000); } // 动态合并单元格 for (int i = 0; i < headnum6.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum6[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } FileOutputStream out = new FileOutputStream(filePath); //保存Excel文件 wb.write(out); //关闭文件流 out.close(); System.out.println("OK!");}

3、效果预览