Java Excel 文件导出导入

特点：

支持xls、xlsx格式；

导出时支持自定义表格标题和表格数据，支持一个文档里多个Sheet；

依赖：

        <!--excel util-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

maven

代码：

  1 import com.fasterxml.jackson.databind.exc.InvalidFormatException;
  2 import com.sensetime.sensestudy.bff.util.vo.StuExpDownloadVO;
  3 import com.sensetime.sensestudy.bff.util.vo.StuTaskDownloadVO;
  4 import lombok.AllArgsConstructor;
  5 import lombok.Data;
  6 import lombok.extern.slf4j.Slf4j;
  7 import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
  8 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 10 import org.apache.poi.ss.usermodel.*;
 11 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
 12 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 13 import org.joda.time.DateTime;
 14 
 15 import java.io.*;
 16 import java.lang.reflect.Field;
 17 import java.nio.file.Files;
 18 import java.nio.file.Paths;
 19 import java.time.Instant;
 20 import java.time.ZoneId;
 21 import java.time.ZonedDateTime;
 22 import java.time.format.DateTimeFormatter;
 23 import java.util.*;
 24 import java.util.regex.Matcher;
 25 import java.util.regex.Pattern;
 26 
 27 
 28 /** 工具类，用于将数据导出到excel文件（可以导出到一个文件的多个Sheet） 或 从excel文件读取数据 */
 29 @Slf4j
 30 public class ExcelUtil {
 31 
 32     public static void main(String[] args) throws IOException {
 33         String fileName = "test.xls";
 34         exportExcel(
 35                 Arrays.asList(
 36                         new SheetInfo<StuTaskDownloadVO>(
 37                                 "class1", ("用户名, 姓名, 完成情况, 提交时间, 分数").split(","),
 38                                 Arrays.asList(new StuTaskDownloadVO("zhangsna", "张三", "未开始", ZonedDateTime.now().toInstant(),  100.0))
 39                         ),
 40                         new SheetInfo<StuTaskDownloadVO>(
 41                                 "class2", ("用户名, 姓名, 完成情况, 提交时间, 分数").split(","), Arrays.asList(
 42                                 new StuTaskDownloadVO("wangwu", "王五", "已完成", ZonedDateTime.now().toInstant(), 20.0),
 43                                 new StuTaskDownloadVO("zhaoliu", "赵六", "未开始", ZonedDateTime.now().toInstant(), 19.0))
 44                         )
 45                 ), Files.newOutputStream(Paths.get("a.xls")));
 46 
 47         exportExcel(
 48                 ("用户名, 姓名, 完成情况, 得分, 评语").split(","),
 49                 Arrays.asList(new StuExpDownloadVO("zhangsna", "张三", "未开始", 22.0, "good"), new StuExpDownloadVO("lisi", "李四", "进行中", 21.0, "bad")),
 50                 Files.newOutputStream(Paths.get("b.xls")));
 51     }
 52 
 53     private ExcelUtil() {
 54     }
 55 
 56     private static final String datetimeFormat = "yyyy-MM-dd HH:mm:ss";
 57 
 58     /**
 59      * @see {@link #exportExcel(String[] headers, Collection dataset, OutputStream out, String datetimeFormat) }
 60      */
 61     public static <T> void exportExcel(String[] headers, Collection<T> dataSet, OutputStream out) {
 62         exportExcel(headers, dataSet, out, datetimeFormat);
 63     }
 64 
 65 
 66     /**
 67      * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定输出流。<br>
 68      * <br>
 69      * <b>输出后会关闭输出流</b>
 70      *
 71      * @param headers        表格属性列名数组。注意：数组中列名的顺序须与待导出的javabean属性的定义顺序一致
 72      * @param dataset        需要显示的数据集合，集合中java bean对象的所有非继承属性的值均会被输出。此方法支持的
 73      *                       javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
 74      * @param out            与输出设备关联的流对象，可以将 EXCEL 文档导出到本地文件或者网络中
 75      * @param datetimeFormat 如果有时间数据，设定输出格式。
 76      */
 77     public static <T> void exportExcel(String[] headers, Collection<T> dataset, OutputStream out, String datetimeFormat) {
 78         exportExcel(Arrays.asList(new SheetInfo("sheet 0", headers, dataset)), out, datetimeFormat);
 79     }
 80 
 81     public static <T> void exportExcel(List<SheetInfo<T>> sheetInfos, OutputStream out) {
 82         exportExcel(sheetInfos, out, datetimeFormat);
 83     }
 84 
 85     /**
 86      * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定输出流。<br>
 87      * <br>
 88      * <b>输出后会关闭输出流</b>
 89      *
 90      * @param sheetInfos         EXCEL 文档中的Sheet信息定义
 91      * @param out            与输出设备关联的流对象，可以将 EXCEL 文档导出到本地文件或者网络中
 92      * @param datetimeFormat 如果有时间数据，设定输出格式。默认会采用服务器所在时区
 93      */
 94     public static <T> void exportExcel(List<SheetInfo<T>> sheetInfos, OutputStream out, String datetimeFormat) {
 95         sheetInfos = Optional.ofNullable(sheetInfos).orElse(Collections.emptyList());
 96         // 生成Excel文档
 97         Workbook workbook = generateWorkbook();
 98         // 生成表头样式
 99         CellStyle tableHeaderStyle = generateTableHeaderStyle(workbook);
100         // 生成表体样式
101         CellStyle tableBodyStyle = generateTableBodyStyle(workbook);
102         // 时间值格式化工具
103         DateTimeFormatter sdf = DateTimeFormatter.ofPattern(datetimeFormat).withZone(ZoneId.systemDefault());
104         // 生成Excel文档中的sheet
105         for (int siIndex = 0; siIndex < sheetInfos.size(); siIndex++) {
106             SheetInfo sheetInfo = sheetInfos.get(siIndex);
107             String[] headers = sheetInfo.getHeaders();
108             //创建sheet
109             Sheet sheet = workbook.createSheet();
110             workbook.setSheetName(siIndex, sheetInfo.getName());
111             sheet.setDefaultColumnWidth(18);
112             // 声明一个绘制图片的顶级管理器
113             Drawing patriarch = sheet.createDrawingPatriarch();
114             // 定义注释的大小和位置
115             // HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
116             // 0, 0, 0, (short) 4, 2, (short) 6, 5));
117             // 设置注释内容
118             // comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
119             // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
120             // comment.setAuthor("sensetime");
121 
122             // 填充表格标题行
123             int rowIndex = 0;
124             Row row = sheet.createRow(rowIndex);
125             for (int i = 0; i < headers.length; i++) {
126                 Cell cell = row.createCell(i);
127                 cell.setCellStyle(tableHeaderStyle);
128                 cell.setCellValue(generateRichTextString(headers[i]));
129             }
130             rowIndex++;
131 
132             // 填充表格数据行
133             Iterator<T> rowDataIt = sheetInfo.getDataset().iterator();
134             Field[] fields = null;
135             while (rowDataIt.hasNext()) {
136                 row = sheet.createRow(rowIndex);
137                 T t = (T) rowDataIt.next();
138                 // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
139                 if (null == fields) {
140                     fields = t.getClass().getDeclaredFields();
141                 }
142                 for (int i = 0; i < fields.length && i < headers.length; i++) {
143                     //HSSFCell cell = row.createCell(i);
144                     Cell cell = row.createCell(i);
145                     cell.setCellStyle(tableBodyStyle);
146 
147                     Field field = fields[i];
148                     field.setAccessible(true);
149                     try {
150                         Object value = field.get(t);
151                         // 判断值的类型后进行强制类型转换
152                         String textValue = null;
153                         // if (value instanceof Integer) {
154                         // int intValue = (Integer) value;
155                         // cell.setCellValue(intValue);
156                         // } else if (value instanceof Float) {
157                         // float fValue = (Float) value;
158                         // textValue = new HSSFRichTextString(
159                         // String.valueOf(fValue));
160                         // cell.setCellValue(textValue);
161                         // } else if (value instanceof Double) {
162                         // double dValue = (Double) value;
163                         // textValue = new HSSFRichTextString(
164                         // String.valueOf(dValue));
165                         // cell.setCellValue(textValue);
166                         // } else if (value instanceof Long) {
167                         // long longValue = (Long) value;
168                         // cell.setCellValue(longValue);
169                         // }
170                         if (value == null) {
171                             textValue = "";
172                         } else if (value instanceof Date || value instanceof Instant) {
173                             Instant instant = null;
174                             if (value instanceof Date) {
175                                 Date date = (Date) value;
176                                 instant = date.toInstant();
177                             } else {
178                                 instant = (Instant) value;
179                             }
180                             textValue = sdf.format(instant);
181                         } else if (value instanceof byte[]) {
182                             // 有图片时，设置行高为60px;
183                             row.setHeightInPoints(60);
184                             // 设置图片所在列宽度为80px,注意这里单位的一个换算
185                             sheet.setColumnWidth(i, (short) (35.7 * 80));
186                             sheet.autoSizeColumn(i);
187                             byte[] bsValue = (byte[]) value;
188                             HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, rowIndex, (short) 6, rowIndex);
189                             anchor.setAnchorType(ClientAnchor.AnchorType.byId(2));
190                             patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
191                         } else {
192                             // 其它数据类型都当作字符串简单处理
193                             textValue = value.toString();
194                         }
195                         // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
196                         if (textValue != null) {
197                             Pattern p = Pattern.compile("^//d+(//.//d+)?$");
198                             Matcher matcher = p.matcher(textValue);
199                             if (matcher.matches()) {
200                                 // 是数字则当作double处理
201                                 cell.setCellValue(Double.parseDouble(textValue));
202                             } else {
203                                 cell.setCellValue(generateRichTextString(textValue));
204                             }
205                         }
206                     } catch (Exception e) {
207                         log.error(e.getMessage(), e);
208                     }
209                 }
210                 rowIndex++;
211             }
212         }
213 
214 
215         try {
216             workbook.write(out);
217         } catch (IOException e) {
218             log.error(e.getMessage(), e);
219         } finally {// 清理资源
220             if (null != workbook) {
221                 try {
222                     workbook.close();
223                 } catch (IOException e) {
224                     log.error(e.getMessage(), e);
225                 }
226             }
227             if (null != out) {
228                 try {
229                     out.close();
230                 } catch (IOException e) {
231                     log.error(e.getMessage(), e);
232                 }
233             }
234         }
235     }
236 
237     /**
238      * 读取excel文件内容，存入到字节流。每行结尾加了换行符，一行中各字段后跟一逗号来分隔
239      */
240     public static ByteArrayInputStream importExcel(InputStream in, int sheetIndex) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
241         ByteArrayOutputStream baos = new ByteArrayOutputStream();
242         try (Workbook workbook = generateWorkbookFromInputStream(in)) {
243             Sheet sheet = workbook.getSheetAt(sheetIndex);
244             for (int ri = sheet.getFirstRowNum(); ri <= sheet.getLastRowNum(); ri++) {
245                 Row row = sheet.getRow(ri);
246                 for (int ci = row.getFirstCellNum(); ci < row.getLastCellNum(); ci++) {
247                     Cell cell = row.getCell(ci);
248                     String value = getValue(cell) + ", ";
249                     baos.write(value.getBytes());
250                     // System.out.printf("%s ", value);
251                 }
252                 baos.write("\n".getBytes());
253                 // System.out.println("");
254             }
255         } catch (Exception e) {
256             log.error(e.getMessage(), e);
257         }
258         ;
259 
260 
261         return new ByteArrayInputStream(baos.toByteArray());
262     }
263 
264     private static String getValue(Cell cell) {
265         if (cell.getCellType() == CellType.BOOLEAN) {
266             // 返回布尔类型的值
267             return String.valueOf(cell.getBooleanCellValue());
268         } else if (cell.getCellType() == CellType.NUMERIC) {
269             // 返回数值类型的值
270             return String.valueOf(cell.getNumericCellValue());
271         } else {
272             // 返回字符串类型的值
273             return String.valueOf(cell.getStringCellValue());
274         }
275     }
276 
277     /**
278      * excepl 文档中的一个Sheet的信息定义
279      */
280     @Data
281     @AllArgsConstructor
282     public static class SheetInfo<T> {
283         private String name;//表格名称
284         private String[] headers;//表格列名
285         private Collection<T> dataset;//表格数据，每个元素代表一行
286     }
287 
288 
289     //以下为为了同时兼容 xls、xlsx 格式的excel文档而定义的方法
290     /**
291      * 是否生成xlsx文档，为true则生成xlsx格式的文档（Excel2007及以后的版本，扩展名是.xlsx）、否则生成xls格式的文档（Excel2003及以前的版本，扩展名是.xls）。
292      */
293     private static final boolean USE_XLSX = true;
294 
295     private static RichTextString generateRichTextString(String val) {
296         RichTextString res = USE_XLSX ? new XSSFRichTextString(val) : new HSSFRichTextString(val);
297         //res.applyFont(new xx);
298         return res;
299     }
300 
301 
302     /**
303      * 创建一个excel文档
304      */
305     private static Workbook generateWorkbook() {
306         //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
307         //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
308         Workbook res = USE_XLSX ? new XSSFWorkbook() : new HSSFWorkbook();
309         return res;
310     }
311 
312     /**
313      * 创建一个excel文档
314      */
315     private static Workbook generateWorkbookFromInputStream(InputStream in) throws IOException {
316         //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
317         //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
318         Workbook res = USE_XLSX ? new XSSFWorkbook(in) : new HSSFWorkbook(in);
319         return res;
320     }
321 
322     private static CellStyle generateTableHeaderStyle(Workbook workbook) {
323         CellStyle style = workbook.createCellStyle();
324         style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
325         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
326         style.setBorderBottom(BorderStyle.THIN);
327         style.setBorderLeft(BorderStyle.THIN);
328         style.setBorderRight(BorderStyle.THIN);
329         style.setBorderTop(BorderStyle.THIN);
330         style.setAlignment(HorizontalAlignment.CENTER);
331         Font font = workbook.createFont();
332         font.setColor(IndexedColors.WHITE.getIndex());
333         font.setFontHeightInPoints((short) 12);
334         //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
335         style.setFont(font);
336 
337         return style;
338     }
339 
340     private static CellStyle generateTableBodyStyle(Workbook workbook) {
341         CellStyle style = workbook.createCellStyle();
342         style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
343         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
344         style.setBorderBottom(BorderStyle.THIN);
345         style.setBorderLeft(BorderStyle.THIN);
346         style.setBorderRight(BorderStyle.THIN);
347         style.setBorderTop(BorderStyle.THIN);
348         style.setAlignment(HorizontalAlignment.CENTER);
349         style.setVerticalAlignment(VerticalAlignment.CENTER);
350         Font font = workbook.createFont();
351         font.setColor(IndexedColors.GREY_80_PERCENT.index);
352         //font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
353         style.setFont(font);
354 
355         return style;
356     }
357 
358 }

View Code

posted @ 2022-07-15 19:20 March On 阅读(230) 评论(0) 编辑收藏举报

刷新页面返回顶部

MarchOn

【好记性不如烂笔头】、【众纷繁技术多同宗，当透过现象看本质】

Java Excel 文件导出导入