说明工具类并不适合所有导入导出场景,需要根据具体的业务进行修改
poi版本3.13
maven配置
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
package com.xinhai.caiyun.commonmanager.api;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SwSbExcelUtil {
public static final short TEN = 10;
public static final short START_ROW=1;//表格开始行
public static final short START_COLUMN=1;//表格开始列
public static final short TITLE_COUNT=2;//表格头部信息终止行数
public static final short TABLE_RULE_COUNT=9;//表格填写规则终止行数
public static final short TABLE_DATA_ROW=11;//表格数据内容起始行数
/**
* 创建excel文档,
*
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
* @param title 标题
* @return Workbook Workbook对象
*/
public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String[] columnNames, String title,String[] rules) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet("sheet1");
// 手动设置列宽。第一个参数表示要为第几列设;第二个参数表示列的宽度,n为列高的像素数。
for (int i = 0; i <=keys.length; i++) {
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
//设置头部信息
setTitleTable(sheet,wb,title,keys.length);
//设置表格提醒填入规则
setTableRule(sheet,wb,rules,keys.length);
//设置表头
setTableHeader(sheet,wb,columnNames);
//填写数据
setTableData(sheet,wb,list,keys);
//
return wb;
}
private static void setTableHeader(Sheet sheet, Workbook wb, String[] columnNames) {
// 创建表头行,从第三行开始
Row row = sheet.createRow(TABLE_RULE_COUNT+1);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
Font f = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints(TEN);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置列名
for (int i = 0; i < columnNames.length; i++) {
Cell cell = row.createCell(i+1);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
}
private static void setTableData(Sheet sheet, Workbook wb, List<Map<String, Object>> list, String[] keys) {
CellStyle cs = wb.createCellStyle();
Font f = wb.createFont();
// 创建第二种字体样式(用于值)
f.setFontHeightInPoints(TEN);
f.setColor(IndexedColors.BLACK.getIndex());
// 设置第二种单元格的样式(用于值)
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置每行每列的值
int size = list.size() +TABLE_DATA_ROW; // 共有多少行值(能用的,第一行不可用)
for (short i = TABLE_DATA_ROW; i < size; i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow(i);
// 在row行上创建一个方格
for (short j = 0; j < keys.length; j++) {
Cell cell = row1.createCell(j+START_COLUMN);
// 省去第一行的sheet数据,从list(1)开始取数据
cell.setCellValue(list.get(i - TABLE_DATA_ROW).get(keys[j]) == null ? " " : list
.get(i -TABLE_DATA_ROW).get(keys[j]).toString());
cell.setCellStyle(cs);
}
}
}
private static void setTableRule(Sheet sheet, Workbook wb, String[] rules, int length) {
CellStyle cs = wb.createCellStyle();
Font f = wb.createFont();
f.setColor(IndexedColors.RED.getIndex());
f.setBold(true);
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setWrapText(true);
cs.setFont(f);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
int startRow=TITLE_COUNT+1;
CellRangeAddress cra =new CellRangeAddress(startRow, TABLE_RULE_COUNT, START_COLUMN, length); // 起始行, 终止行, 起始列, 终止列
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(1,cra, sheet,wb); // 下边框
RegionUtil.setBorderLeft(1, cra, sheet,wb); // 左边框
RegionUtil.setBorderRight(1, cra, sheet,wb); // 有边框
sheet.addMergedRegion(cra);
StringBuilder value=new StringBuilder();
value.append("填写规则\r\n");
for (String rule:rules)
{
value.append(rule+"\r\n");
}
Row row=sheet.getRow(startRow);
Cell cell= row.createCell(START_COLUMN);
cell.setCellStyle(cs);
cell.setCellValue(value.toString());
startRow++;
}
private static void setTitleTable( Sheet sheet,Workbook wb,String title,Integer length)
{
Row titleRow=sheet.createRow((short)1);
//设置表格开头
// 合并单元格
CellRangeAddress cra =new CellRangeAddress(START_ROW, TITLE_COUNT, START_COLUMN, length); // 起始行, 终止行, 起始列, 终止列
sheet.addMergedRegion(cra);
// 使用RegionUtil类为合并后的单元格添加边框
RegionUtil.setBorderBottom(1,cra, sheet,wb); // 下边框
RegionUtil.setBorderLeft(1, cra, sheet,wb); // 左边框
RegionUtil.setBorderRight(1, cra, sheet,wb); // 有边框
RegionUtil.setBorderTop(1, cra, sheet,wb); // 上边框
CellStyle titleCs = wb.createCellStyle();
Font tileFont = wb.createFont();
tileFont.setBold(true);
tileFont.setColor(IndexedColors.DARK_RED.getIndex());
tileFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
tileFont.setFontHeightInPoints((short) 12);
titleCs.setFont(tileFont);
titleCs.setAlignment(CellStyle.ALIGN_CENTER);
titleCs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleCs.setBorderTop(CellStyle.BORDER_THIN);
titleCs.setBorderBottom(CellStyle.BORDER_THIN);
titleCs.setBorderLeft(CellStyle.BORDER_THIN);
titleCs.setBorderRight(CellStyle.BORDER_THIN);
Cell titleCell=titleRow.createCell(1);
titleCell.setCellValue(title);
titleCell.setCellStyle(titleCs);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet
* 要设置的sheet.
* @param textlist
* 下拉框显示的内容
* @param firstRow
* 开始行
* @param endRow
* 结束行
* @param firstCol
* 开始列
* @param endCol
*/
public static void setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint
.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(
regions, constraint);
sheet.addValidationData(data_validation_list);
}
public static List<List<String>> importSwsbData(InputStream is) throws IOException {
List<List<String>>list=new ArrayList<>();
HSSFWorkbook wb = new HSSFWorkbook(is);
HSSFSheet sheet = wb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
if(rows>0) {
for(int i=TABLE_DATA_ROW;i<=rows;i++) {
List<String>dataList=new ArrayList<>();
HSSFRow row = sheet.getRow(i);
if(row!=null){
int totalCol=row.getPhysicalNumberOfCells();//获取改行总列数
if(totalCol>0) {
Map<String,Object>map=new HashMap<>();
for(int j=1;j<=totalCol;j++){
String value=getCel(row.getCell(j));//获取每个单元格的值
dataList.add(value);
}
}
list.add(dataList);
}
}
}
return list;
}
/**
* 获取每列值
* @param cell
* @return
*/
private static String getCel(Cell cell) {
String cellValue = null;
HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值
return cellValue;
}
}