当前位置: 首页 > 工具软件 > EXCEL-UTIL4J > 使用案例 >

ExcelUtils

慕宏儒
2023-12-01
package com.pay.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.TypeReference;

public class JsonToExcelUtil {

    public static void main(String[] args) throws JsonParseException, JsonMappingException, IOException {

        String jsonStr = "{}";
        Map<String,Object> mapdata = new ObjectMapper().readValue(jsonStr,
                 new TypeReference<Map<String,Object>>() {
                 });

        ArrayList<LinkedHashMap<String, Object>> data = (ArrayList<LinkedHashMap<String, Object>>) mapdata.get("data");

        LinkedHashSet<String> titles = data.stream().flatMap(row -> row.entrySet().stream()).map(Entry::getKey)
                    .collect(Collectors.toCollection(LinkedHashSet::new));

        SXSSFWorkbook res = exportExcel(titles,data);

        FileOutputStream fileout = new FileOutputStream(new File("E://tmp/data1.xlsx"));

            res.write(fileout);

    }

     public static SXSSFWorkbook exportExcel(Map<String, Object> titles, ArrayList<LinkedHashMap<String, Object>> data) {
            List<String> headerName = titles.entrySet().stream().map(t -> String.valueOf(t.getValue())).collect(Collectors.toList());
            List<String> tiltleKeys = new ArrayList<>(titles.keySet());

            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
            Sheet sheet = workbook.createSheet("sheet1");
            Row titleRow = sheet.createRow(0);
            if (titles.isEmpty()) {
                return workbook;
            }

            CellStyle headerStyle = getHeaderStyle(workbook);
            IntStream.range(0, headerName.size()).forEach(i -> {
                Cell cell = titleRow.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValkue(headerName.get(i));
            });

            CellStyle cellStyle = getCellStyle(workbook);
            IntStream.range(0, data.size()).forEach(j -> {
                Map<String, Object> stringObjectMap = data.get(j);
                Row detail = sheet.createRow(j + 1);
                IntStream.range(0, tiltleKeys.size()).forEach(i -> {
                    String string = tiltleKeys.get(i);
                    Cell cell = detail.createCell(i);
                    String o = String.valueOf(stringObjectMap.get(string));
                    if (StringUtils.isNotBlank(o)) {
                        cell.setCellValue(String.valueOf(o));
                    }
                    cell.setCellStyle(cellStyle);
                });
            });
            return workbook;
        }

        private static CellStyle getHeaderStyle(SXSSFWorkbook workbook) {
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontName("Courier New");
            CellStyle style = workbook.createCellStyle();
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setRightBorderColor(HSSFColor.BLACK.index);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setTopBorderColor(HSSFColor.BLACK.index);
            style.setFont(font);
            style.setWrapText(false);
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            return style;
        }

        public static CellStyle getCellStyle(SXSSFWorkbook workbook) {
            Font font = workbook.createFont();
            font.setFontName("Courier New");
            CellStyle style = workbook.createCellStyle();
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setRightBorderColor(HSSFColor.BLACK.index);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setTopBorderColor(HSSFColor.BLACK.index);
            style.setFont(font);
            style.setWrapText(false);
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            return style;
        }

        public static SXSSFWorkbook exportExcel(Set<String> titles, ArrayList<LinkedHashMap<String, Object>> data) {
            Map<String, Object> map = titles.stream().collect(Collectors.toMap(s -> s, s -> s, (a, b) -> b, LinkedHashMap::new));
            return exportExcel(map, data);
        }

}
 类似资料:

相关阅读

相关文章

相关问答