本文共计2078个文字，预计阅读时间需要9分钟。
javaExcelUtil package com.skywares.safety.utils;import FileInputStream;import FileOutputStream;import IOException;import InputStream;import OutputStream;import PushbackInputStream;import java.sql.*;
ExcelUtil
package com.skywares.safety.utils;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletInputStream;
import javax.servlet.ServletOutputStream;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.google.common.base.Strings;
public class ExcelUtil {
/**
* 导入excel文件,返回List
>
*
* @Author 不告诉你
* @date 2016-09-09 15:25:35
* @param Object
* in 数据输出流(FileInputStream,ServletInputStream)
* @return
*/
public static List
> importExcel(Object in) throws Exception { Workbook tempWorkbook = null; Sheet fromsheet = null; Row fromRow = null; Cell fromCell = null; Row headerRow = null; Cell headerCell = null; List
> list = new ArrayList<>(); if (in instanceof InputStream) { tempWorkbook = create((InputStream) in); }else if (in instanceof FileInputStream) { tempWorkbook = create((FileInputStream) in); }else if (in instanceof ServletInputStream) { tempWorkbook = create((ServletInputStream) in); }else { return null; } for (int i = 0; i < tempWorkbook.getNumberOfSheets(); i++) { fromsheet = tempWorkbook.getSheetAt(i); if (fromsheet == null || fromsheet.getLastRowNum() == 0 || fromsheet.getLastRowNum() < 3) { continue; } removeRow(fromsheet, 1); headerRow = fromsheet.getRow(0); for (int k = 1; k < fromsheet.getLastRowNum() + 1; k++) { Map
map = new HashMap
(); fromRow = fromsheet.getRow(k); if (fromRow == null || fromRow.getPhysicalNumberOfCells() == 0) { continue; } for (int j = headerRow.getFirstCellNum(); j < headerRow.getPhysicalNumberOfCells(); j++) { fromCell = fromRow.getCell(j); headerCell = headerRow.getCell(j); if (fromCell == null || headerCell == null) { continue; } String headerCellValue = headerCell.getStringCellValue(); String[] headerCellValues = headerCellValue.split("\\."); if (Strings.isNullOrEmpty(headerCellValue) || headerCellValues.length < 2) { continue; } int cType = fromCell.getCellType(); switch (cType) { case Cell.CELL_TYPE_STRING: map.put(headerCellValues[1], fromCell.getRichStringCellValue().toString().trim()); break; case Cell.CELL_TYPE_NUMERIC: map.put(headerCellValues[1], fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: map.put(headerCellValues[1], fromCell.getCellFormula()); break; case Cell.CELL_TYPE_BOOLEAN: map.put(headerCellValues[1], fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: map.put(headerCellValues[1], fromCell.getErrorCellValue()); break; default: map.put(headerCellValues[1], fromCell.getRichStringCellValue().toString().trim()); break; } } list.add(map); } } tempWorkbook.close(); return list; } /** * 根据excel模版创建文件(map
或者javaBean或者ResultSet数据源) * * @Author 不告诉你 * @date 2016-09-05 11:11:11 * @param String * tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007) * @param String * sheetName 生成工作表的名字(为空时,默认为sheet1) * @param Object * data map
或者javaBean或者ResultSet数据源 * @param Object * os 数据输出流(FileOutputStream,ServletOutputStream) * @return */ public static void paddingExcel(String tempPath, String sheetName, Object data, Object os) throws Exception { tempExportExcel(tempPath, sheetName, null, null, os, data); } /** * 根据excel模版创建文件(model) * * @Author 不告诉你 * @date 2016-09-05 11:11:11 * @param String * tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007) * @param String * sheetName 生成工作表的名字(为空时,默认为sheet1) * @param Object * data model List数据源 * @param Object * os 数据输出流(FileOutputStream,ServletOutputStream) * @param Object * headerData 固定数据源map
或者javaBean或者ResultSet * @return */ public static void javaBeanToExcel(String tempPath, String sheetName, Object data, Object os, Object headerData) throws Exception { tempExportExcel(tempPath, sheetName, data, null, os, headerData); } /** * 根据excel模版创建文件(ResultSet rs) * * @Author 不告诉你 * @date 2016-09-05 11:11:11 * @param String * tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007) * @param String * sheetName 生成工作表的名字(为空时,默认为sheet1) * @param ResultSet * rs 结果集 List数据源 * @param Object * os 数据输出流(FileOutputStream,ServletOutputStream) * @param Object * headerData 固定数据源map
或者javaBean或者ResultSet * @return */ public static void resultSetToExcel(String tempPath, String sheetName, ResultSet rs, Object os, Object headerData) throws Exception { tempExportExcel(tempPath, sheetName, null, rs, os, headerData); } private static void tempExportExcel(String tempPath, String sheetName, Object data, ResultSet rs, Object os, Object headerData) throws Exception { if (!System.getProperty("os.name").equalsIgnoreCase("Linux")) { tempPath = tempPath.substring(1); } String version = tempPath.substring(tempPath.lastIndexOf(".")); if (version.equals(".xls")) { tempExport2003Excel(tempPath, sheetName, data, rs, os, headerData); } else if (version.equals(".xlsx")) { tempExport2007Excel(tempPath, sheetName, data, rs, os, headerData); } else { return; } } private static void tempExport2007Excel(String tempPath, String sheetName, Object data, ResultSet rs, Object os, Object headerData) throws Exception { OutputStream out = null; Workbook tempWorkbook = null; Sheet fromsheet = null; Cell fromCell = null; try { if (os instanceof FileOutputStream) { out = (FileOutputStream) os; }else if (os instanceof ServletOutputStream) { out = (ServletOutputStream) os; }else { return; } if (Strings.isNullOrEmpty(sheetName)) { sheetName = "sheet1"; } tempWorkbook = new XSSFWorkbook(OPCPackage.open(new FileInputStream(tempPath))); fromsheet = (XSSFSheet) tempWorkbook.getSheetAt(0); if (fromsheet == null || fromsheet.getLastRowNum() == 0) { return; } tempWorkbook.setSheetName(0, sheetName); if (headerData != null) { int firstrow = fromsheet.getFirstRowNum(); int lastrow = fromsheet.getLastRowNum(); for (int i = firstrow; i < lastrow; i++) { fromCell = fromsheet.getRow(i).getCell(0); String start = fromCell.getStringCellValue(); if ("data.start".equalsIgnoreCase(start.trim())) { lastrow = i; break; } if (i == (lastrow - 1)) { return; } } paddingExcel(fromsheet, firstrow, lastrow, headerData); } if (data != null || rs != null) { int firstrow = fromsheet.getFirstRowNum(); int lastrow = fromsheet.getLastRowNum(); for (int i = firstrow; i < lastrow; i++) { fromCell = fromsheet.getRow(i).getCell(0); String start = fromCell.getStringCellValue(); if ("data.start".equalsIgnoreCase(start.trim())) { firstrow = i; break; } if (i == (lastrow - 1)) { return; } } if (data != null) { copySheets(fromsheet, firstrow, lastrow, data, null); } if (rs != null) { copySheets(fromsheet, firstrow, lastrow, null, rs); } removeRow(fromsheet, firstrow); removeRow(fromsheet, firstrow); removeRow(fromsheet, firstrow); } tempWorkbook.write(out); out.flush(); tempWorkbook.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } @SuppressWarnings("resource") private static void tempExport2003Excel(String tempPath, String sheetName, Object data, ResultSet rs, Object os, Object headerData) throws Exception { OutputStream out = null; Workbook tempWorkbook = null; Sheet fromsheet = null; Cell fromCell = null; try { if (os instanceof FileOutputStream) { out = (FileOutputStream) os; }else if (os instanceof ServletOutputStream) { out = (ServletOutputStream) os; }else { return; } if (Strings.isNullOrEmpty(sheetName)) { sheetName = "sheet1"; } tempWorkbook = new HSSFWorkbook(new FileInputStream(tempPath)); fromsheet = (HSSFSheet) tempWorkbook.getSheetAt(0); if (fromsheet == null || fromsheet.getLastRowNum() == 0) { return; } tempWorkbook.setSheetName(0, sheetName); if (headerData != null) { int firstrow = fromsheet.getFirstRowNum(); int lastrow = fromsheet.getLastRowNum(); for (int i = firstrow; i < lastrow; i++) { fromCell = fromsheet.getRow(i).getCell(0); String start = fromCell.getStringCellValue(); if ("data.start".equalsIgnoreCase(start.trim())) { lastrow = i; break; } if (i == (lastrow - 1)) { return; } } paddingExcel(fromsheet, firstrow, lastrow, headerData); } if (data != null || rs != null) { int firstrow = fromsheet.getFirstRowNum(); int lastrow = fromsheet.getLastRowNum(); for (int i = firstrow; i < lastrow; i++) { fromCell = fromsheet.getRow(i).getCell(0); String start = fromCell.getStringCellValue(); if ("data.start".equalsIgnoreCase(start.trim())) { firstrow = i; break; } if (i == (lastrow - 1)) { return; } } if (data != null) { copySheets(fromsheet, firstrow, lastrow, data, null); } if (rs != null) { copySheets(fromsheet, firstrow, lastrow, null, rs); } removeRow(fromsheet, firstrow); removeRow(fromsheet, firstrow); removeRow(fromsheet, firstrow); } tempWorkbook.write(out); out.flush(); tempWorkbook.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } @SuppressWarnings("unchecked") private static void copySheets(Sheet fromsheet, int firstrow, int lastrow, Object data, ResultSet rs) throws Exception { if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) { return; } Row fromRow = null; Row newRow = null; Cell newCell = null; Cell fromCell = null; RichTextString textValue = null; CellStyle fromCellStyle = null; fromCell = fromsheet.getRow(firstrow + 2).getCell(0); String end = fromCell.getStringCellValue(); if (!"data.end".equalsIgnoreCase(end.trim())) { return; } if (data != null) { int iRow = firstrow + 3; List
