导出excel
hutool-excel-并设置单元格格式为文本
public static void exportExcel(HttpServletResponse response, List<?> list, Map<String, String> headerAlias, String reportName) {
if ((reportName == null) && (list == null) && (headerAlias == null)) {
throw new RuntimeException("输入参数不对");
}
//通过工具类创建writer，默认创建xls格式
ExcelWriter writer = ExcelUtil.getWriter();
//设置sheet的名称
writer.renameSheet(reportName);
//自定义标题别名
headerAlias.forEach(
(k, v) -> {
writer.addHeaderAlias(k, v);
}
);
//一次性写出内容，使用默认样式，强制输出标题
writer.write(list, true);
//获取整个Excel的样式，设置单元格格式为文本
StyleSet styleSet = writer.getStyleSet();
CellStyle cellStyle = styleSet.getCellStyleForNumber();
DataFormat format = writer.getWorkbook().createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
writer.setStyleSet(styleSet);
//设置所有列为自动宽度，不考虑合并单元格 此方法必须在指定列数据完全写出后调用才有效。
//writer.autoSizeColumnAll();
setSizeColumn(writer.getSheet(), 17);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
response.setHeader("Content-Disposition", "attachment;filename=" + new String(reportName.getBytes("gb2312"), "ISO8859-1") + ".xls");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
ServletOutputStream out = null;
getServletOutputStream(response, writer, out);
}private static void getServletOutputStream(HttpServletResponse response, ExcelWriter writer, ServletOutputStream out) {
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
// 关闭writer，释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(out);
}
}以下为业务代码：
@ApiOperation(value = "导出Excel")
@GetMapping("/export2")
public void exportExcel(HttpServletResponse response, @RequestParam String outputbillid){
projectDeliveryDetailCmdService.exportExcel(response, outputbillid);
}/**
* 获取数据，生成excel
* @param outputBillId 出库主单ID
* @author quxingtao
* @Date 2022-06-30 15:28:05
* @Version 1.0
* */
public void exportExcel(HttpServletResponse response, String outputBillId){
//sheet页的名称
String reportName = "子库转移明细";
//列名
String[] colName = {"X1-序号","ISMODELPRODUCTSTR-配置类型", "MATERIALCODE-物料编码", "MATERIALNAME-物料名称", "PROVIDERPRODUCTNAME-供应商产品名称", "MEASURENAME-计量单位", "APPLYAMOUNT-申请数量", "TOTALMOUNT-总数量", "PRICE-单价", "MONEY-金额",
"BATCH-批次", "WAREHOUSELOCATIONFULLNAME-货位", "BOXNUMBER-箱件号", "PROVIDERNAME-供应商", "PROJECTCODE-项目编号", "PROJECTNAME-项目名称", "IMPORTPROJECTCODE-目标项目编号", "IMPORTPROJECTNAME-目标项目名称", "ENTITYDESC-备注"};
//这四个为数字
String[] numCol = {"APPLYAMOUNT", "TOTALMOUNT", "PRICE", "MONEY"};
//查询数据库，获取实体数据
List<Map<String, Object>> list = this.findExcelList(outputBillId);
int num = 1;
for (Map<String, Object> map:list){
//数字为null则设置为0
for (int i = 0; i < numCol.length; i++){
if (map.get(numCol[i]) == null){
map.put(numCol[i],"0");
}
}
//手写的excel中的序号
map.put("X1",num+"");
num++;
//业务需求，库中字段进行翻译
if (map.get("ISMODELPRODUCTSTR") != null) {
String ismodelproductstr = map.get("ISMODELPRODUCTSTR").toString();
if ("1".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","动态BOM");
} else if ("2".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","静态BOM");
} else if ("3".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","虚拟BOM");
} else if ("0".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","单一产品");
} else if ("4".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","配置清单");
} else if ("5".equals(ismodelproductstr)) {
map.put("ISMODELPRODUCTSTR","二级辅材");
}
} else {
map.put("ISMODELPRODUCTSTR","单一产品");
}
}
//设置列名
Map<String, String> head = new LinkedHashMap<>();
for (int i =0; i < colName.length ;i++){
head.put(colName[i].split("-")[0], colName[i].split("-")[1]);
}
ExcelUtils.exportExcel(response, list, head, reportName);
}//查询数据库，利用jpa。生成list<Map<string,string>>形式
/**
* 获取数据，生成excel
* @param outputBillId 出库主单ID
* @author quxingtao
* @Date 2022-06-30 15:28:05
* @Version 1.0
* */
public List<Map<String, Object>> findExcelList(String outputBillId){
StringBuffer sql = new ExportExcelViewSql().LOCATION_SHIFT_EXCEL_VIEW;
sql.append(" where nvl(o.parent_output_detail_id,0) = 0 and o.output_bill_id = ").append(outputBillId);
sql.append(" order by o.id ");
Query query = entityManager.createNativeQuery(sql.toString());
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.getResultList();
}
原生poi写法
public ReturnResult<String> exportExcel(HttpServletResponse response, String masterId) throws IOException {
ServletOutputStream out = response.getOutputStream();
//获取模板
ReturnResult<String> returnResult = plisFeignClient.getFileId("downLoad", "SHIP_DELIVERNOTICE");
if (returnResult.getCode() != ReturnResultCode.SUCCESS || returnResult.getData() == null || returnResult.getData() == "") {
throw new StockParseException("获取附件id失败:" + returnResult.getMessage());
}
String fileId = returnResult.getData();
Response responseInventory = plisFeignClient.download(fileId, "bpc-web");
if (responseInventory.status() == 404) {
throw new StockParseException("调用下载文件接口失败:404");
}
if (responseInventory == null) {
throw new StockParseException("模板下载失败");
}
//获取模板信息
Map<String, Collection<String>> headers = responseInventory.headers();
Collection<String> collection = headers.get("content-disposition");
if (collection == null) {
throw new StockParseException("模板下载失败");
}
String coll = collection.toArray()[0].toString();
Response.Body body = responseInventory.body();
//获取文件名
String filleNameDec = coll.split(";")[1].split("=")[1];
InputStream inputStream = null;
String fileName = "";
try {
fileName = URLDecoder.decode(filleNameDec, "UTF-8");
inputStream = body.asInputStream();
} catch (Exception e) {
e.printStackTrace();
}
if (inputStream == null) {
throw new StockParseException("获取模板失败:空文件");
}
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
//2003
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith("xlsx")) {
//2007 及2007以上
workbook = new XSSFWorkbook(inputStream);
}
workbook = parseExcel(workbook, masterId);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String[] fileNames = filleNameDec.split("\\.");
response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileNames[0].getBytes("gb2312"), "ISO8859-1") + ".xls");
workbook.write(out);
out.flush();
//response.getOutputStream().flush();
} catch (Exception e) {
return ReturnResult.error(e.getMessage());
} finally {
inputStream.close();
workbook.close();
out.close();
}
return ReturnResult.success("success");
}private Workbook parseExcel(Workbook workbook, String masterId){
Sheet sheet = workbook.getSheet("签收装箱清单");
List<PackInfoDetail> packDetailList = findPackDetailByPage4Excel(masterId);
int rows = 2;
for (int i = 0; i < packDetailList.size(); i++) {
PackInfoDetail scmPackDetail = packDetailList.get(i);
rows++;
//在模板的第三行开始创建数据，
Row row = sheet.createRow(rows);
for (int j = 0; j < 40; j++) {
row.createCell(j);
}
sheet.getRow(rows).getCell(0).setCellValue(String.valueOf(i + 1));
sheet.getRow(rows).getCell(1).setCellValue(scmPackDetail.getId());
sheet.getRow(rows).getCell(2).setCellValue(scmPackDetail.getOrderDetailId() == null ? "" : scmPackDetail.getOrderDetailId());
sheet.getRow(rows).getCell(3).setCellValue(scmPackDetail.getIsModelProduct() == null ? "" : getModelProductName(scmPackDetail.getIsModelProduct()));
sheet.getRow(rows).getCell(4).setCellValue(scmPackDetail.getBomMark() == null ? "" : scmPackDetail.getBomMark());
String bom2Name = "";
if ("5".equals(scmPackDetail.getIsModelProduct())) {
bom2Name = scmPackDetail.getBom2Name() == null ? "" : scmPackDetail.getBom2Name();
}
sheet.getRow(rows).getCell(5).setCellValue(bom2Name);
PackInfo packInfo = null;
if (scmPackDetail.getPackInfoId() != null) {
Optional<PackInfo> byId = packInfoDao.findById(scmPackDetail.getPackInfoId());
packInfo = byId.orElse(null);
}
sheet.getRow(rows).getCell(6).setCellValue(packInfo == null ? "" : packInfo.getBoxNumber());
sheet.getRow(rows).getCell(7).setCellValue(packInfo == null ? "" : packInfo.getProductName());
sheet.getRow(rows).getCell(8).setCellValue(scmPackDetail.getProjectId() == null ? "" : scmPackDetail.getProjectCode());
sheet.getRow(rows).getCell(9).setCellValue(scmPackDetail.getProjectId() == null ? "" : scmPackDetail.getProjectCode());
sheet.getRow(rows).getCell(10).setCellValue(scmPackDetail.getSiteCode() == null ? "" : scmPackDetail.getSiteCode());
sheet.getRow(rows).getCell(11).setCellValue(scmPackDetail.getSiteName() == null ? "" : scmPackDetail.getSiteName());
sheet.getRow(rows).getCell(12).setCellValue(scmPackDetail.getBuyProductName());
String productMaterialId = scmPackDetail.getProductMaterialId();
String materialCode = "";
String materialName = "";
if(StringUtils.isNotEmpty(productMaterialId)) {
ReturnResult<Material> materialById = materialFeignClient.getMaterialById(productMaterialId);
if (materialById.getCode()==ReturnCodeEnum.SUCCESS.getCode()){
materialCode = materialById.getData().getEntityCode();
materialName = materialById.getData().getEntityName();
}
}
sheet.getRow(rows).getCell(13).setCellValue(materialCode);
sheet.getRow(rows).getCell(14).setCellValue(materialName);
sheet.getRow(rows).getCell(15).setCellValue(scmPackDetail.getMaterialPropertyId() == null ? "" : scmPackDetail.getMaterialPropertyId());
String strIsMain = "";
if (StringUtils.isNotEmpty(scmPackDetail.getIsMainProduct()) && "1".equals(scmPackDetail.getIsMainProduct())) {
strIsMain = "是";
} else if (StringUtils.isNotEmpty(scmPackDetail.getIsMainProduct()) && "2".equals(scmPackDetail.getIsMainProduct())) {
strIsMain = "BOM产品";
} else {
strIsMain = "否";
}
sheet.getRow(rows).getCell(16).setCellValue(strIsMain);
sheet.getRow(rows).getCell(17).setCellValue(scmPackDetail.getMeasureName());
Double amountBom = scmPackDetail.getCountRelation() == null ? new Double(0.0D) : scmPackDetail.getCountRelation().doubleValue();
sheet.getRow(rows).getCell(18).setCellValue(amountBom);
Double amount = scmPackDetail.getAmount() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getAmount());
sheet.getRow(rows).getCell(19).setCellValue(amount);
Double price = scmPackDetail.getPrice() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getPrice());
sheet.getRow(rows).getCell(20).setCellValue(price);
Double money = scmPackDetail.getMoney() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getMoney());
sheet.getRow(rows).getCell(21).setCellValue(money);
sheet.getRow(rows).getCell(22).setCellValue(scmPackDetail.getBuyApplyCode() == null ? "" : scmPackDetail.getBuyApplyCode());
sheet.getRow(rows).getCell(23).setCellValue(scmPackDetail.getProviderId() == null ? "" : fingProviderById(scmPackDetail.getProviderId()));
if (scmPackDetail.getProductionDate() == null) {
sheet.getRow(rows).getCell(24).setCellValue("");
} else {
sheet.getRow(rows).getCell(24).setCellValue(scmPackDetail.getProductionDate().toString());
}
sheet.getRow(rows).getCell(25).setCellValue(scmPackDetail.getProviderProductCode());
sheet.getRow(rows).getCell(26).setCellValue(scmPackDetail.getProviderProductName());
sheet.getRow(rows).getCell(27).setCellValue(scmPackDetail.getDescription());
if(packInfo !=null && packInfo.getLength() != null){
sheet.getRow(rows).getCell(28).setCellValue(Double.valueOf(packInfo.getLength()));
} else {
sheet.getRow(rows).getCell(28).setCellValue("");
}
if(packInfo !=null && packInfo.getWidth() != null){
sheet.getRow(rows).getCell(29).setCellValue(Double.valueOf(packInfo.getWidth()));
} else {
sheet.getRow(rows).getCell(29).setCellValue("");
}
if(packInfo !=null && packInfo.getHeight() != null){
sheet.getRow(rows).getCell(30).setCellValue(Double.valueOf(packInfo.getHeight()));
} else {
sheet.getRow(rows).getCell(30).setCellValue("");
}
if(packInfo !=null && packInfo.getVolume() != null){
sheet.getRow(rows).getCell(31).setCellValue(Double.valueOf(packInfo.getVolume()));
} else {
sheet.getRow(rows).getCell(31).setCellValue("");
}
if(packInfo !=null && packInfo.getGrossWeight() != null){
sheet.getRow(rows).getCell(32).setCellValue(Double.valueOf(packInfo.getGrossWeight()));
} else {
sheet.getRow(rows).getCell(32).setCellValue("");
}
sheet.getRow(rows).getCell(33).setCellValue(packInfo == null ? "" : packInfo.getProductMeasureName());
if(packInfo != null && packInfo.getProductAmount() != null){
sheet.getRow(rows).getCell(34).setCellValue(Double.valueOf(packInfo.getProductAmount().toString()));
} else {
sheet.getRow(rows).getCell(34).setCellValue("");
}
//DeliverNoticeServiceImpl 879
if(packInfo != null && packInfo.getBoxAmount() != null){
sheet.getRow(rows).getCell(35).setCellValue(Double.valueOf(packInfo.getBoxAmount()));
} else {
sheet.getRow(rows).getCell(34).setCellValue("");
}
if(packInfo != null && packInfo.getProductTotalAmount() != null){
sheet.getRow(rows).getCell(36).setCellValue(Double.valueOf(packInfo.getProductTotalAmount()));
} else {
sheet.getRow(rows).getCell(36).setCellValue("");
}
if(packInfo != null && packInfo.getProductTotalWeight() != null){
sheet.getRow(rows).getCell(37).setCellValue(Double.valueOf(packInfo.getProductTotalWeight()));
} else {
sheet.getRow(rows).getCell(37).setCellValue("");
}
if(packInfo != null && packInfo.getProductTotalVolumn() != null){
sheet.getRow(rows).getCell(38).setCellValue(Double.valueOf(packInfo.getProductTotalVolumn()));
} else {
sheet.getRow(rows).getCell(38).setCellValue("");
}
sheet.getRow(rows).getCell(39).setCellValue(packInfo == null ? "" : packInfo.getDescription());
//sheet.setRowView(rows, 350);
}
return workbook;
}导入excel
@ApiOperation(value = "期初导入")
@PostMapping("/returnImportExcels/{inputBillId}")
public ReturnResult<String> returnImportExcels(@RequestParam("file") MultipartFile file, @PathVariable String inputBillId){
return inputBillExcelImportService.returnImportExcels(file, inputBillId);
}public ReturnResult<String> returnImportExcels(MultipartFile file, String inputBillId){
Workbook workbook = ParseExcelUtil.getWorkBook(file);
//判断入库主单是否存在
Optional<InputBill> byId = inputStorageDao.findById(inputBillId);
InputBill inputBill = byId.orElseThrow(() -> new StockParseException("未找到该主单"));
//保存数据字典基础信息
return ReturnResult.success(parseExcel(workbook, inputBill));
}public String parseExcel(Workbook workbook, InputBill inputBill) {
boolean isERP = true;
List<InputBillDetail> resultDataList = new LinkedList();
// 解析sheet
Sheet sheet = workbook.getSheetAt(0);
// 获取第一行数据
int firstRowNum = sheet.getFirstRowNum();
Row firstRow = sheet.getRow(firstRowNum);
if (null == firstRow) {
log.error("解析Excel失败，在第一行没有读取到任何数据！");
throw new StockParseException("模板异常");
}
// 解析每一行的数据，构造数据对象 从excel第三行开始拿数据
int rowStart = firstRowNum + 3;
int rowEnd = sheet.getLastRowNum();//获取的最后一行为什么是20
for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (null == row) {
continue;
}
if (ParseExcelUtil.isRowEmpty(row)) {
continue;
}
InputBillDetail inputBillDetail = convertRowToData(row, inputBill);
if (null == inputBillDetail) {
log.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");
continue;
}else if (isERP == true){
resultDataList.add(inputBillDetail);
}
}
inputStorageDao.save(inputBill);
Iterator<InputBillDetail> iterator = resultDataList.iterator();
while (iterator.hasNext()){
InputBillDetail next = iterator.next();
inputStorageDetailDao.save(next);
}
return "success";
}private InputBillDetail convertRowToData(Row row, InputBill inputBill) {
InputBillDetail inputBillDetail = new InputBillDetail();
//SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
int rowNum = row.getRowNum() + 1;
Cell cell;
//每行有17列
for (int i = 1; i <= 17; i++) {
String cellValue = ParseExcelUtil.getCellValue(row.getCell(i));
if (StrUtil.containsBlank(cellValue)) {
throw new StockParseException("第 " + rowNum + "行第" + (i + 1) + "列数据不合法(不能包含空格)");
}
}
//第0列是序号 从1开始
int cellNum = 1;
//库存组织编码 --B
cell = row.getCell(cellNum++);
String warehouseNum = ParseExcelUtil.getCellValue(cell);
//仓库
String inputWarehouseId = inputBill.getInputWarehouseId();
Optional<Warehouse> byId = warehouseDao.findById(inputWarehouseId);
Warehouse warehouse = byId.orElseThrow(() -> new StockParseException("未查询到仓库"));
//是否主配置 --G
cell = row.getCell(cellNum++);
String isMainProduct = ParseExcelUtil.getCellValue(cell);
if (!"是".equals(isMainProduct) && !"否".equals(isMainProduct)) {
throw new StockParseException("第 " + rowNum + "行的“是否主配置”只能填写：“是”或“否” ！");
}
inputBillDetail.setIsMainProduct("是".equals(isMainProduct) ? "1" : "0");
//金额 --K
cell = row.getCell(cellNum++);
System.out.println("==================cell2："+cell.toString()+","+cell.getCellTypeEnum());
//获取小数自动为四舍五入，设置单元格格式为文本
cell.setCellType(CellType.STRING);
String money = ParseExcelUtil.getCellValue(cell);
System.out.println("==========金额：" + money);
inputBillDetail.setMoney(money);
return inputBillDetail;
}1.字体样式常用方法
/**
* 方法描述: 设置基础字体样式字体 这里保留最基础的样式使用
*
* @param workbook 工作簿
* @param bold 是否粗体
* @param fontName 字体名称
* @param fontSize 字体大小
* @return org.apache.poi.ss.usermodel.Font
* @author wqf
* @date 2021/5/19 15:58
*/
public static Font setBaseFont(Workbook workbook, boolean bold, boolean italic, String fontName, int fontSize) {
Font font = workbook.createFont();
//设置字体名称 宋体 / 微软雅黑 /等
font.setFontName(fontName);
//设置是否斜体
font.setItalic(italic);
//设置字体高度
//font.setFontHeight((short) fontHeight);
//设置字体大小 以磅为单位
font.setFontHeightInPoints((short) fontSize);
//设置是否加粗
font.setBold(bold);
//默认字体颜色
// font.setColor(Font.COLOR_NORMAL);
//红色
//font.setColor(Font.COLOR_RED);
//设置下划线样式
//font.setUnderline(Font.ANSI_CHARSET);
//设定文字删除线
//font.setStrikeout(true);
return font;
}
全局样式设置
private static StyleSet GlobalStyleSet(ExcelWriter writer, Workbook workbook,Font font) {
//全局样式设置
StyleSet styleSet = writer.getStyleSet();
CellStyle cellStyle = styleSet.getCellStyle();
//设置全局文本居中
styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
//设置全局字体样式
styleSet.setFont(font);
//设置背景颜色 第二个参数表示是否将样式应用到头部
styleSet.setBackgroundColor(IndexedColors.WHITE, true);
//设置自动换行 当文本长于单元格宽度是否换行
styleSet.setWrapText();
// 设置全局边框样式
styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
return styleSet;
}
头部标题样式
//设置全局样式
StyleSet styleSet = GlobalStyleSet(writer, workbook);
//设置头部标题样式
CellStyle headCellStyle = styleSet.getHeadCellStyle();
//水平居中
headCellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体样式
headCellStyle.setFont(setBaseFont(workbook, true, false, "宋体", 12));
writer.setStyleSet(styleSet);
数字保留小数
例：保留两位小数
CellStyle cellStyleForNumber = styleSet.getCellStyleForNumber();
cellStyleForNumber.setDataFormat((short)2);
5.时间格式化
例如格式为：YYYY/MM/dd 格式
CellStyle cellStyleForDate = styleSet.getCellStyleForDate();
//14 代表的时间格式是 yyyy/MM/dd
cellStyleForDate.setDataFormat((short)14);
时间格式占时只看到这一种格式常见些，像yyyy-MM-dd 格式都没找到，就只有在写入数据写先处理下时间格式了。
行（Row）样式
//获取输出构造器 设置工作簿名称
ExcelWriter writer = ExcelUtil.getWriterWithSheet(sheetName);
Workbook workbook = writer.getWorkbook();
Sheet sheet = writer.getSheet();
Row row = sheet.getRow(rowIndex);
if(sheet.getRow(rowIndex )==null){
//rowIndex 表示的是第几行，例：创建第二行，rowIndex=1
sheet.createRow(rowIndex );
}
//创建样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle .setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle .setAlignment(HorizontalAlignment.LEFT);
cellStyle .setFont(setBaseFont(workbook, true, false, "宋体", 12));
cellStyle .setBorderBottom(BorderStyle.THIN);
cellStyle .setBorderLeft(BorderStyle.THIN);
cellStyle .setBorderRight(BorderStyle.THIN);
cellStyle .setBorderTop(BorderStyle.THIN);
//应用样式到某一行（
row .setRowStyle(cellStyle );
//应用样式到某一行 （或者这样写） rowIndex 表示的是第几行
//writer.setRowStyle(rowIndex ,cellStyle );
单元格（Cell）样式
Row row = sheet.getRow(rowIndex);
if(sheet.getRow(rowIndex )==null){
//rowIndex 表示的是第几行，例：创建第二行，rowIndex=1
sheet.createRow(rowIndex );
}
//创建本行的第几个单元格 cellIndex=0 表示第一个
if(row.get(cellIndex)==null){
row .createCell(cellIndex)；
}
//创建样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle .setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle .setAlignment(HorizontalAlignment.LEFT);
cellStyle .setFont(setBaseFont(workbook, true, false, "宋体", 12));
cellStyle .setBorderBottom(BorderStyle.THIN);
cellStyle .setBorderLeft(BorderStyle.THIN);
cellStyle .setBorderRight(BorderStyle.THIN);
cellStyle .setBorderTop(BorderStyle.THIN);
//应用样式到某一行（
row .setRowStyle(cellStyle );
//应用样式到某一行 （或者这样写） rowIndex 表示的是第几行
//writer.setRowStyle(rowIndex ,cellStyle );
合并单元格
//处理标题行 合并某行的单元格，并写入对象到单元格,如果写到单元格中的内容非null，行号自动+1，否则当前行号不变
//主要有两种方式
1. writer.merge(cellIndex, content, true);
表示当前行 合并从第一个单元到cellIndex+1个单元，并填充内容content，第三个参数表示是否将头部标题样式应用到这里。
或者
2.writer.merge(startRowIndex,endRowIndex, startCellIndex, endCellIndex, content, false);
表示和并第startRowIndex+1行到endRowIndex+1行 ，并合并从第endCellIndex+1个单元到endCellIndex+1个单元格，并填充content内容，最后一个字段表示是否将头部标题样式应用到这里。
列表别名
//LinkedHashMap 中的数据是根据put先后顺序来的，HashMap数据时无序的。
//使用方法 writer.setHeaderAlias(headerAlias); 时如果使用HashMap 可能展示的数
//据顺序会错乱
Map<String, String> headerAlias = new LinkedHashMap<>();
headerAlias.put(字段名1, 列名1);
headerAlias.put(字段名2, 列名2);
headerAlias.put(字段名3, 列名3);
headerAlias.put(字段名4, 列名4);
headerAlias.put(字段名5, 列名5);
writer.setHeaderAlias(headerAlias);
//或者一项一项设置列的别名 列别名顺序会跟代码中addHeaderAlias顺序一致
writer.addHeaderAlias(字段名1,列名1);
writer.addHeaderAlias(字段名2,列名2);
列宽问题
8.1 自动列宽
/**
* 自适应宽度(中文支持)
* @param sheet
* @param size 因为for循环从0开始，size值为 列数-1
*/
public static void setSizeColumn(Sheet sheet, int size) {
for (int columnNum = 0; columnNum <= size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
8.2 手动列宽设置
//表示 第一列的列宽是15
writer.setColumnWidth(0, 15);
另外常用方法
//跳过当前行 即当前行不写内容
writer.passCurrentRow();
//定位到最后一行，常用于在末尾追加数据
writer.setCurrentRowToEnd();
10 . 下载excel代码 在数据都填充完成后，调用该方法即可
/**
* 方法描述: 下载excel文件
*
* @param response 响应
* @param fileName 文件名称
* @param writer writer
* @return void
* @author wqf
* @date 2021/5/24 16:20
*/
private static void downloadExcel(HttpServletResponse response, String fileName, ExcelWriter writer) {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
ServletOutputStream out = null;
try {
// 设置请求头属性
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
out = response.getOutputStream();
// 写出到文件
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
// 此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
log.error(e.getMessage());
e.printStackTrace();
}
}
excel 添加下拉框
CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 5, 5);
DataValidationHelper helper = sheet.getDataValidationHelper();
// 设置下拉框数据
String[] str = new String[]{"男", "女","阴阳人"};
DataValidationConstraint constraint = helper.createExplicitListConstraint(str);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writer.addValidationData(dataValidation);
12.背景色填充
//示例：将单元格背景填充为黄色
short index = IndexedColors.YELLOW.index;
cellStyle.setFillForegroundColor(index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
总结 ： 整合了一个工具类，仅作参考
/**
* @Author: wqf
* @Date: 2021/05/28
* @Description: hutool 工具导出excel(非填充模板，手工画)
*/
@Slf4j
public class HutoolExcelUtil {
/**
* YYYY/MM/dd 时间格式
*/
private static final short LOCAL_DATE_FORMAT_SLASH = 14;
/**
* 方法描述: 创建excel
*
* @param isXlsx excel文件类型 true-xlsx/false-xls
* @return cn.hutool.poi.excel.ExcelWriter
* @author wqf
* @date 2021/6/1 9:47
*/
public static ExcelWriter createExcel(boolean isXlsx) {
return ExcelUtil.getWriter(isXlsx);
}
/**
* 方法描述: 全局基础样式设置
* 默认 全局水平居中+垂直居中
* 默认 自动换行
* 默认单元格边框颜色为黑色，细线条
* 默认背景颜色为白色
*
* @param writer writer
* @param font 字体样式
* @return cn.hutool.poi.excel.StyleSet
* @author wqf
* @date 2021/5/28 10:43
*/
public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font) {
//全局样式设置
StyleSet styleSet = writer.getStyleSet();
//设置全局文本居中
styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
//设置全局字体样式
styleSet.setFont(font, true);
//设置背景颜色 第二个参数表示是否将样式应用到头部
styleSet.setBackgroundColor(IndexedColors.WHITE, true);
//设置自动换行 当文本长于单元格宽度是否换行
//styleSet.setWrapText();
// 设置全局边框样式
styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
return styleSet;
}
/**
* 方法描述: 设置标题的基础样式
*
* @param styleSet StyleSet
* @param font 字体样式
* @param horizontalAlignment 水平排列方式
* @param verticalAlignment 垂直排列方式
* @return org.apache.poi.ss.usermodel.CellStyle
* @author wqf
* @date 2021/5/28 10:16
*/
public static CellStyle createHeadCellStyle(StyleSet styleSet, Font font,
HorizontalAlignment horizontalAlignment,
VerticalAlignment verticalAlignment) {
CellStyle headCellStyle = styleSet.getHeadCellStyle();
headCellStyle.setAlignment(horizontalAlignment);
headCellStyle.setVerticalAlignment(verticalAlignment);
headCellStyle.setFont(font);
return headCellStyle;
}
/**
* 方法描述: 设置基础字体样式字体 这里保留最基础的样式使用
*
* @param bold 是否粗体
* @param fontName 字体名称
* @param fontSize 字体大小
* @return org.apache.poi.ss.usermodel.Font
* @author wqf
* @date 2021/5/19 15:58
*/
public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {
Font font = writer.getWorkbook().createFont();
//设置字体名称 宋体 / 微软雅黑 /等
font.setFontName(fontName);
//设置是否斜体
font.setItalic(italic);
//设置字体大小 以磅为单位
font.setFontHeightInPoints((short) fontSize);
//设置是否加粗
font.setBold(bold);
return font;
}
/**
* 方法描述: 设置行或单元格基本样式
*
* @param writer writer
* @param font 字体样式
* @param verticalAlignment 垂直居中
* @param horizontalAlignment 水平居中
* @return void
* @author wqf
* @date 2021/5/28 10:28
*/
public static CellStyle createCellStyle(ExcelWriter writer, Font font, boolean wrapText,
VerticalAlignment verticalAlignment,
HorizontalAlignment horizontalAlignment) {
CellStyle cellStyle = writer.getWorkbook().createCellStyle();
cellStyle.setVerticalAlignment(verticalAlignment);
cellStyle.setAlignment(horizontalAlignment);
cellStyle.setWrapText(wrapText);
cellStyle.setFont(font);
return cellStyle;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
