POIUtil（动态复杂表头、动态数据、多线程、合并数据列的POI导出成Excel工具附带生成csv文件）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

package com.sckj.base.util;
 
import java.io.IOException;
import java.lang.reflect.Constructor;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorCompletionService;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
 
import javax.servlet.http.HttpServletResponse;
 
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 
public class POIUtil {
 
    /**
     * POI导出Excel表格（多行表头、合并单元格）
     *
     * @param rowTotalNum    表头总行数
     * @param colTotalNum    表头列数
     * @param sheetName      表单名称
     * @param fileName       导出文件名称(带文件后缀)
     * @param headerNameList 表头字段名称集合,按顺序传入每行的字段名称,特别注意：每行字段名称为一个数组,合并单元格时字段跨几列就将该字段重复几次
     * @param headerNumList  表头字段 行列位置集合，按顺序传入以上对应字段的行列占用位置,特别注意：每行字段行列位置为一个数组,"0,2,0,0"
     *                       ===> “起始行，截止行，起始列，截止列”
     * @param dataList       数据
     * @param perPageNum     每个sheet页自定义显示条数
     * @param isMerge        是否需要合并相同数据列(如果设置为false，那么columns里设置什么都不会起作用)
     * @param startRow       除开表头的开始行
     * @param columns        合并的列的序号(0开始)
     * @param clazz      业务逻辑类(写业务方法的类)
     * @return HSSFWorkbook
     * @author 47Gamer
     * @see POI导出每页sheet最多容纳100万左右条数据，设置了perPageNum,每个sheet页显示自定义条数
     * @see 所有参数必传
     */
    public static void exportExcel(Integer rowTotalNum, Integer colTotalNum, String fileName, String sheetName,
                                   List<String[]> headerNameList, List<String[]> headerNumList, List<Object> dataList, Integer perPageNum,
                                   boolean isMerge, Integer startRow, int[] columns, Class clazz, HttpServletResponse response) {
        try {
            /** 执行总条数 */
            int total = 0;
 
            /** 本机线程数，根据该值来决定开启多少个线程 */
            int nThreads = Runtime.getRuntime().availableProcessors();
 
            /** 通过nthreads来创建一个线程池 */
            ExecutorService executorService = Executors.newFixedThreadPool(nThreads);
 
            /** 通过线程池来创建一个完成服务，泛型为完成服务返回的类型 */
            ExecutorCompletionService<Integer> completionService = new ExecutorCompletionService<Integer>(
                    executorService);
 
            /** 表头名称 */
            String[] headerNames = {};
            /** 表头行列坐标位置 */
            String[] headerNums = {};
            /** 工作空间 */
            Workbook wb = new SXSSFWorkbook();
            /** 表单行 */
            Row row = null;
            /** 表单页数 */
            Sheet sheet = null;
            /** 表头样式 */
            CellStyle style = null;
            /** 表头字体 */
            Font font = null;
            /** 自定义分组 */
            List<List<Object>> groupList = ListUtils.partition(dataList, perPageNum);
 
            if (CollectionUtils.isNotEmpty(groupList)) {
                for (int n = 0; n < groupList.size(); n++) {
                    sheet = wb.createSheet(sheetName + n);
                    ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();
                    for (int i = 0; i < rowTotalNum; i++) {
                        headerNames = headerNameList.get(i);
                        headerNums = headerNumList.get(i);
 
                        style = wb.createCellStyle();
                        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        style.setBorderBottom(BorderStyle.THIN);
                        style.setBorderLeft(BorderStyle.THIN);
                        style.setBorderRight(BorderStyle.THIN);
                        style.setBorderTop(BorderStyle.THIN);
                        style.setAlignment(HorizontalAlignment.CENTER);
                        style.setVerticalAlignment(VerticalAlignment.CENTER);
 
                        font = wb.createFont();
                        font.setFontName("微软雅黑");
                        font.setFontHeightInPoints((short) 10);
                        font.setBold(true);
                        style.setFont(font);
 
                        /** 每行表头 */
                        row = sheet.createRow(i);
                        for (int j = 0; j < headerNames.length; j++) {
                            ((SXSSFSheet) sheet).autoSizeColumn((short) j);
                            Cell cell = row.createCell((short) j);
                            cell.setCellValue(headerNames[j]);
                            cell.setCellStyle(style);
                        }
 
                        /** 动态合并单元格 */
                        for (int l = 0; l < headerNums.length; l++) {
                            ((SXSSFSheet) sheet).autoSizeColumn((short) l, true);
                            String[] temp = headerNums[l].split(",");
                            Integer startrow = Integer.parseInt(temp[0]);
                            Integer overrow = Integer.parseInt(temp[1]);
                            Integer startcol = Integer.parseInt(temp[2]);
                            Integer overcol = Integer.parseInt(temp[3]);
                            if (!(startrow.equals(overrow) && startcol.equals(overcol))) {
                                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
                            }
                        }
                    }
                    List<Object> subList = new ArrayList<>();
                    dataList = groupList.get(n);
                    /** 数据总量 */
                    int listSize = dataList.size();
                    /** 每条线程处理的初始数据条数 */
                    int toIndex = 0;
                    if (listSize < nThreads) nThreads = listSize;
                    toIndex = listSize / nThreads;
                    int startRowIndex = rowTotalNum;
                    for (int i = 0; i < nThreads + 1; i++) {
                        if (toIndex * i + toIndex > listSize) {
                            subList = dataList.subList(toIndex * i, listSize);
                        } else {
                            subList = dataList.subList(toIndex * i, toIndex * (i + 1));
                        }
                        Constructor<?> constructor = clazz.getConstructor(List.class, Sheet.class, int.class, int.class, Workbook.class);
                        @SuppressWarnings("unchecked")
                        Future<Integer> future = completionService
                                .submit((Callable<Integer>) constructor.newInstance(subList, sheet, startRowIndex, colTotalNum, wb));
                        startRowIndex = future.get();
                        total = startRowIndex;
                    }
                    System.out.println("第" + (n + 1) + "次执行总条数 : " + (total - rowTotalNum));
                    /** 是否合并单元格 */
                    if (isMerge) {
                        for (int i = 0; i < columns.length; i++) {
                            int cellNum = columns[i];
                            int lastRowNum = sheet.getLastRowNum();
                            int currentRow = startRow;
                            for (int j = startRow; j <= lastRowNum; j++) {
                                Cell cell = sheet.getRow(j).getCell(cellNum);
                                String cellValue = cell.getStringCellValue();
                                String nextValue = getNextCellValueString(cellNum, sheet.getRow(j + 1));
                                /** 将当前单元格值 置空直到最后一个 */
                                cell.setCellValue("");
                                if (cellValue.equals(nextValue)) {
                                    continue;
                                } else {
                                    /** 获取左上角的单元格,合并后只保存左上角单元格的值 */
                                    Cell leftTopCell = sheet.getRow(currentRow).getCell(cellNum);
                                    leftTopCell.setCellValue(cellValue);
                                    /** 多个cell相同合并 */
                                    if (currentRow != j) {
                                        sheet.addMergedRegion(new CellRangeAddress(currentRow, j, cellNum, cellNum));
                                        currentRow = j + 1;
                                    } else {
                                        currentRow += 1;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            executorService.shutdown();
            response.setContentType("application/vnd.ms-excel; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
            response.setCharacterEncoding("utf-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 获取下一个单元格的值
     *
     * @param cellNum 单元格列数
     * @param nextRow 下一行数
     */
    private static String getNextCellValueString(int cellNum, Row nextRow) {
        String nextValue;
        if (nextRow != null) {
            Cell nextCell = nextRow.getCell(cellNum);
            if (nextCell != null) {
                nextValue = nextCell.getStringCellValue();
            } else {
                nextValue = "";
            }
        } else {
            nextValue = "";
        }
        return nextValue;
    }
 
}

Excel转为CSV文件：

public static void excel2Csv(String excelPath, String csvPath) {
        File outputFile = new File(csvPath);
        File inputFile = new File(excelPath);
        StringBuffer data = new StringBuffer();
        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            Row row = null;
            Cell cell = null;
            Workbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
            int sheetNum = wBook.getNumberOfSheets();
            for (int i=0; i <sheetNum; i++) {
                Sheet sheet = wBook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        cell = cellIterator.next();
                        switch (cell.getCellType()) {
                            case BOOLEAN:
                                data.append(cell.getBooleanCellValue() + "|");
                                break;
                            case NUMERIC:
                                data.append(cell.getNumericCellValue() + "|");
                                break;
                            case STRING:
                                data.append(cell.getStringCellValue() + "|");
                                break;
                            case BLANK:
                                data.append("" + "|");
                                break;
                            default:
                                data.append(cell + "|");
                        }
                    }
                    data.append("\r\n");
                }
            }
            fos.write(data.toString().getBytes("UTF-8"));
            fos.close();
        } catch (Exception ioe) {
            LOGGER.error("excel2Csv, {}",ioe);
        }
    }

导入的jar：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-collections4</artifactId>
   <version>4.3</version>
</dependency><br><br><br><br>###################################其实还可以优化，本人今后在博客里用JDK8优化下代码和线程#############################