1.ExportExcelUtil工具类
package com.cgp.excelexport.utils;
import com.cgp.excelexport.pojo.Person;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
public class ExportExcelUtil {
public static void ExportExcel(List<Person> list, String[] titles) {
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
//给单元格设置样式
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//设置字体加粗
font.setBold(true);
//给字体设置样式
cellStyle.setFont(font);
//设置单元格背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置单元格填充样式(使用纯色背景颜色填充)
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
for (int i = 0; i < titles.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(cellStyle);
//设置列的宽度
sheet.setColumnWidth(i, 200*50);
}
for (int j = 0; j < list.size(); j++) {
Row rowData = sheet.createRow(j + 1);
Person person = list.get(j);
Cell cell = rowData.createCell(0);
cell.setCellValue(person.getId());
Cell cell2 = rowData.createCell(1);
cell2.setCellValue(person.getName());
Cell cell3 = rowData.createCell(2);
cell3.setCellValue(person.getAge());
Cell cell4 = rowData.createCell(3);
cell4.setCellValue(person.getPhone());
}
String fileName = "D:/人员信息导出.xlsx";
try {
FileOutputStream fileOutputStream = new FileOutputStream(fileName);
wb.write(fileOutputStream);
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2.测试导出
package com.cgp.excelexport.test;
import com.cgp.excelexport.dao.PersonDao;
import com.cgp.excelexport.pojo.Person;
import com.cgp.excelexport.utils.ExportExcelUtil;
import java.util.List;
public class ExportExcelTest {
public static void main(String[] args) {
PersonDao personDao = new PersonDao();
List<Person> list = personDao.getPersonList();
String[] titles = {"编号","姓名","年龄","手机号"};
ExportExcelUtil.ExportExcel(list, titles);
System.out.println("人员数据导出成功！");
}
}
3.扩展
1.锁定单元格
//----------------关键代码----------------------------
SSFSheet sheet = xwb.createSheet();
//不保护锁定不生效
sheet.protectSheet("test");
// 单元格样式锁定
CellStyle lockStyle = xwb.createCellStyle();
lockStyle.setLocked(true);
// 单元格样式不锁定
CellStyle unlockStyle = xwb.createCellStyle();
unlockStyle.setLocked(false);
//对应锁定样式给到单元格即可实现锁定
2.锁定单元格
//----------------关键代码----------------------------
//下拉框
String[] status = new String[] {" 自持","未售","签约"};
String[] saleCategoryNames = new String[]{"住宅","商业","公寓","办公","车位","其他"};
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(status);
XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(saleCategoryNames);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
//xssfrow 最大行数为1048576，需要减去列
addressList = new CellRangeAddressList(1, 1048575, 5, 5);//范围坐标
validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
addressList = new CellRangeAddressList(1, 1048575, 4, 4);//范围坐标
validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint2, addressList);
sheet.addValidationData(validation);
//对应锁定样式给到单元格即可实现锁定
4.通过模板导出
//----------------关键代码----------------------------
//读取模板文件
String classpath = this.getClass().getResource("/").getPath();
String webappRoot = classpath.replaceFirst("/","")
.replaceAll("WEB-INF/", "")
.replaceAll("classes/", "")
.replaceAll("pd/", "");//根据实际路径修改
log.info("房间导出-模板路径： " + webappRoot+"pd/excel/room.xlsx");
File templateFile = new File(webappRoot+"pd/excel/room.xlsx");
InputStream input = new FileInputStream(templateFile);
XSSFWorkbook xwb = new XSSFWorkbook(input);
XSSFSheet sheet = xwb.getSheetAt(0);
