<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
2.工具类
package com.study.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* 生成ExcelUtils 工具
*/
@SuppressWarnings("all")
public class ExcelUtils {
/**
* Excell 生成
* @param dataLisst
* @param sheetTitle
* @return
* @throws PlatException
*
*/
public static HSSFWorkbook getExcelHSSFWorkbook(Map<String, Object> headMap , List<Map<String, Object>> dataList , String sheetTitle) throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet();
hssfWorkbook.setSheetName(0, sheetTitle);
// 合并单元格 ,自定义抽取方法
// sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 2));
// 设置 列头
HSSFRow headRow = sheet.createRow(0);
// 标题样式
CellStyle headCellStyle = getHeadCellStyle(hssfWorkbook);
Set<String> keySet = headMap.keySet();
int index = 0;
for (String key : keySet) {
HSSFCell hssfCell = headRow.createCell(index);
hssfCell.setCellStyle(headCellStyle);
hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
hssfCell.setCellValue((String) headMap.get(key));
index++;
}
// 数据单元格样式
HSSFCellStyle dataCellStyle = getDataCellStyle(hssfWorkbook);
// 填充 数据
for (int i = 1; i <= dataList.size(); i++) {
index = 0;
HSSFRow row = sheet.createRow(i);
Map<String, Object> resultMap = (Map<String, Object>) dataList.get(i - 1);
for (Iterator keys = headMap.keySet().iterator(); keys.hasNext(); ) {
HSSFCell cell = row.createCell(index);
cell.setCellValue((String) resultMap.get(keys.next()));
cell.setCellStyle(dataCellStyle);
index++;
}
}
return hssfWorkbook;
}
/**
* 获取 标题单元格样式
* @param workbook
* @return
*/
public static HSSFCellStyle getHeadCellStyle(HSSFWorkbook workbook){
// 标题单元格样式
HSSFCellStyle headCellStyle = (HSSFCellStyle)workbook.createCellStyle();
headCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
headCellStyle.setWrapText(true); // 换行
headCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
headCellStyle.setFillPattern( CellStyle.SOLID_FOREGROUND);
headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 字体
HSSFFont headFont = (HSSFFont) workbook.createFont(); // 使用实现类自己的字体,POI接口中可用性不好
headFont.setFontName("仿宋");
headFont.setFontHeightInPoints((short) 11); // 设置字体高度
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
headCellStyle.setFont(headFont);
return headCellStyle ;
}
/**
* 获取 数据单元格样式
*/
public static HSSFCellStyle getDataCellStyle(Workbook workbook){
// 数据单元格样式
HSSFCellStyle dataCellStyle = (HSSFCellStyle) workbook.createCellStyle();
dataCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
dataCellStyle.setWrapText(true); // 换行
dataCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
// 字体
Font dataFont = workbook.createFont();
dataFont.setFontName("仿宋");
dataFont.setFontHeightInPoints((short) 11);
dataCellStyle.setFont(dataFont);
return dataCellStyle ;
}
}
3,测试类
package com.study.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SuppressWarnings("all")
public class HSSFWorkbookTest {
String path = "E:\\work\\workSpace\\2021-11-01\\easyexcel";
/**
* 07 版Excel
*
* @throws IOException
*/
@Test
public void test07() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet firstSheet = workbook.createSheet("firstSheet");
String[] ColumnNamme = {"COLUMN1", "COLUMN2", "COLUMN3", "COLUMN4", "COLUMN5", "COLUMN6", "COLUMN7", "COLUMN8",
"COLUMN9", "COLUMN10", "COLUMN11", "COLUMN12", "COLUMN13", "COLUMN14", "COLUMN15", "COLUMN16", "COLUMN17",
"COLUMN18"};
String[] ColumnValue1 = {"序号 No.", "钢级 Steel Grade", "炉号 Heat No.", "批号 Batch No.",
"钢板厚度(mm) Plate Thickness", "试样编号 Specimen No.", "试样尺寸", "试样尺寸",
"试样尺寸", "试样尺寸", "试样尺寸", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能", "力学性能"};
String[] ColumnValue2 = {"序号 No.", "钢级 Steel Grade", "炉号 Heat No.", "批号 Batch No.",
"钢板厚度(mm) Plate Thickness", "试样编号 Specimen No.", "厚", "宽",
"面积", "标距长度", "标距长度", "Fe (KN)", "Fm (KN)", "Re", "Rm", "A", "冲击试验KV2", "冲击试验KV2"};
String[] ColumnValue3 = {"序号 No.", "钢级 Steel Grade", "炉号 Heat No.", "批号 Batch No.",
"钢板厚度(mm) Plate Thickness", "试样编号 Specimen No.", "mm", "mm",
"mm*mm", "mm", "mm", "Fe (KN)", "Fm (KN)", "Mpa", "Mpa", "%", "试验名称", "试验值"};
String[] ColumnValue4 = {"序号 No.", "钢级 Steel Grade", "炉号 Heat No.", "批号 Batch No.",
"钢板厚度(mm) Plate Thickness", "试样编号 Specimen No.", "d0或a", "d1或b",
"S0", "L0", "L1", "Fe (KN)", "Fm (KN)", "Mpa", "Mpa", "%", "试验名称", "试验值"};
List<String[]> headList = new ArrayList<>();
headList.add(ColumnValue1);
headList.add(ColumnValue2);
headList.add(ColumnValue3);
headList.add(ColumnValue4);
// 设置 列宽自适应
for (int i = 0; i < ColumnValue1.length; i++) {
firstSheet.autoSizeColumn(i);
firstSheet.setColumnWidth(i, firstSheet.getColumnWidth(i) * 17 / 10);
}
// 列样式
HSSFCellStyle headCellStyle = ExcelUtils.getHeadCellStyle(workbook);
// 列头数组
int ruwNum = 0;
for (String[] columnName : headList) {
HSSFRow row = firstSheet.createRow(ruwNum++);
for (int i = 0; i < columnName.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(headCellStyle);
cell.setCellValue(columnName[i]);
}
}
// 设置多级列头
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 0));
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 1, 1));
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 2, 2));
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 3, 3));
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 4, 4));
firstSheet.addMergedRegion(new CellRangeAddress(0, 3, 5, 5));
// 试样尺寸 力学性能
firstSheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 10));
firstSheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 17));
// mm 标距长度 mm
firstSheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 7));
firstSheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 10));
firstSheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 10));
// Fe Fm
firstSheet.addMergedRegion(new CellRangeAddress(1, 3, 11, 11));
firstSheet.addMergedRegion(new CellRangeAddress(1, 3, 12, 12));
// Mpa %
firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 14));
firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));
// 冲击试验
firstSheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 17));
firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 16, 16));
firstSheet.addMergedRegion(new CellRangeAddress(2, 3, 17, 17));
// 数据
List<Map<String, Object>> dataList = new ArrayList<>();
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("COLUMN1", "981");
dataMap.put("COLUMN2", "AB/DB36");
dataMap.put("COLUMN3", "12F202");
dataMap.put("COLUMN4", "H2135232");
dataMap.put("COLUMN5", 12.0d);
dataMap.put("COLUMN6", "F6984");
dataMap.put("COLUMN7", 12.18);
dataMap.put("COLUMN8", 25.26);
dataMap.put("COLUMN9", 307.7);
dataMap.put("COLUMN10", 98);
dataMap.put("COLUMN11", 125.0);
dataMap.put("COLUMN12", 131);
dataMap.put("COLUMN13", 131);
dataMap.put("COLUMN14", 170);
dataMap.put("COLUMN15", 425);
dataMap.put("COLUMN16", 27.5);
dataMap.put("COLUMN17", "纵向-20Ckv");
dataMap.put("COLUMN18", 150);
for (int i = 0; i < 10; i++) {
dataList.add(dataMap);
}
HSSFCellStyle dataCellStyle = ExcelUtils.getDataCellStyle(workbook);
// 数据填充
for (Map<String, Object> map : dataList) {
HSSFRow row = firstSheet.createRow(ruwNum++);
int cellIndex = 0;
for (String key : ColumnNamme) {
HSSFCell cell = row.createCell(cellIndex++);
cell.setCellStyle(dataCellStyle);
Class<?> aClass = map.get(key).getClass();
if (aClass.equals(Double.class)) {
cell.setCellValue((Double) map.get(key));
} else if (aClass.equals(String.class)) {
cell.setCellValue((String) map.get(key));
} else if (aClass.equals(Integer.class)) {
cell.setCellValue((Integer) map.get(key));
}
}
}
// 生成Excel
FileOutputStream outputStream = new FileOutputStream(path + "Excel-07.xls");
workbook.write(outputStream);
outputStream.close();
System.out.println("excel 文件已经生成 ");
}
}