From 3d2b51ea4520533de5e78f88dddf5b5c7dce4247 Mon Sep 17 00:00:00 2001
From: mitao <2763622819@qq.com>
Date: 星期四, 06 六月 2024 15:56:18 +0800
Subject: [PATCH] 管理后台添加营销员导入功能
---
meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java | 116 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 115 insertions(+), 1 deletions(-)
diff --git a/meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java b/meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java
index c8ae99a..cd74242 100644
--- a/meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java
+++ b/meiya-core/src/main/java/com/sinata/core/util/ExcelExportUtil.java
@@ -1,8 +1,14 @@
package com.sinata.core.util;
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
+import java.io.FileInputStream;
+import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
+import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
@@ -17,6 +23,8 @@
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
+import org.springframework.web.multipart.MultipartFile;
+import org.springframework.web.multipart.commons.CommonsMultipartFile;
/**
* Excel报表导出工具类
@@ -34,7 +42,7 @@
*/
public static void easySheet(String fileName, String sheetName, List<List<Object>> dataList, HttpServletResponse response) throws IOException {
//设置响应头，输出文件
- setResponseHeader(response, fileName);
+ setResponseHeader2(response, fileName);
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = null;
@@ -70,6 +78,10 @@
sheet.setDefaultColumnWidth(15);//setColumnWidth设置cell的宽度
sheet.setDefaultRowHeightInPoints(20);
+ // 设置单元格类型为文本类型
+ HSSFDataFormat dataFormat = workbook.createDataFormat();
+ style.setDataFormat(dataFormat.getFormat("@"));
+
//填充报表数据
for (int y = 0; y < dataList.size(); y++) {
List<Object> cellList = dataList.get(y);
@@ -88,6 +100,92 @@
outStream.flush();
outStream.close();
}
+ /**
+ * 简单模板excel导出功能
+ *
+ * @param fileName 报表文件名
+ * @param sheetName 报表表名
+ * @param dataList 报表数据（包含行头和列内容）
+ * @param response
+ * @throws IOException
+ */
+ public static InputStream easySheet2(String fileName, String sheetName, List<List<Object>> dataList, HttpServletResponse response) throws IOException {
+ //设置响应头，输出文件
+ setResponseHeader2(response, fileName);
+
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ Sheet sheet = null;
+
+ HSSFCellStyle style = workbook.createCellStyle();
+ // 创建字体对象
+ Font ztFont = workbook.createFont();
+ ztFont.setItalic(true); // 设置字体为斜体字
+ ztFont.setColor(Font.COLOR_RED); // 将字体设置为“红色”
+ ztFont.setFontHeightInPoints((short) 22); // 将字体大小设置为18px
+ ztFont.setFontName("华文行楷"); // 将“华文行楷”字体应用到当前单元格上
+ ztFont.setUnderline(Font.U_DOUBLE); // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
+ style.setFont(ztFont); // 将字体应用到样式上面
+
+ // 设置单元格边框样式
+ style.setBorderBottom(CellStyle.BORDER_THICK);
+ style.setBorderTop(CellStyle.BORDER_DASHED);
+ style.setBorderLeft(CellStyle.BORDER_DOUBLE);
+ style.setBorderRight(CellStyle.BORDER_THIN);
+
+ // 设置单元格边框颜色
+ style.setBottomBorderColor(HSSFColor.ORANGE.index);
+ style.setTopBorderColor(HSSFColor.ORANGE.index);
+ style.setLeftBorderColor(HSSFColor.ORANGE.index);
+
+ style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
+ style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
+ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
+
+ // 设置单元格内容垂直对其方式
+ style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+ sheet = workbook.createSheet(sheetName);//设置表明
+ sheet.setDefaultColumnWidth(15);//setColumnWidth设置cell的宽度
+ sheet.setDefaultRowHeightInPoints(20);
+
+ //填充报表数据
+ for (int y = 0; y < dataList.size(); y++) {
+ List<Object> cellList = dataList.get(y);
+ Row row = sheet.createRow(y);
+ for (int x = 0; x < cellList.size(); x++) {
+ row.createCell(x).setCellValue(ToolUtil.toStr(cellList.get(x)));
+ }
+ }
+ ByteArrayOutputStream bos = null;
+ try {
+ bos = new ByteArrayOutputStream();
+ workbook.write(bos);
+ byte[] byteArray = bos.toByteArray();
+ return new ByteArrayInputStream(byteArray);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }finally {
+ if (bos != null) {
+ bos.flush();
+ bos.close();
+ }
+ }
+ /*
+ try {
+ File file = new File("F:\\DeskTop");
+ if (!file.exists()) {
+ file.mkdirs();// 创建文件根目录
+ }
+ String savePath = file.getPath() +"/导入失败数据.xls";
+ OutputStream os = new FileOutputStream(savePath);
+ workbook.write(os);
+ os.flush();
+ os.close();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }*/
+ return null;
+ }
+
/**
* 复杂杂模板excel导出（合并单元格、设置表格样式等）
@@ -270,4 +368,20 @@
e.printStackTrace();
}
}
+ /**
+ * 设置响应头
+ *
+ * @param response
+ */
+ public static void setResponseHeader2(HttpServletResponse response, String excelName) {
+ response.setCharacterEncoding("utf-8");
+ response.setContentType("application/vnd.ms-excel;charset=UTF-8");
+ response.setHeader("Pragma", "no-cache");
+ response.setHeader("Cache-Control", "no-cache");
+ try {
+ response.setHeader("Content-Disposition", "attachment;filename=" + excelName + URLEncoder.encode(".xls", "UTF-8"));
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
}
--
Gitblit v1.7.1