当前位置 : 主页 > 编程语言 > c++ >

java读取excel use poi

来源：互联网 收集：自由互联 发布时间：2021-06-30
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.
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
           javaBean = (List) data; int j=0; for (Object o : javaBean) { j++; Map
            
              map = FormatUtil.javaBeanToMap(o); newRow = fromsheet.createRow(iRow); fromRow = fromsheet.getRow(firstrow + 1); for (int i = fromRow.getFirstCellNum(); i < fromRow.getPhysicalNumberOfCells(); i++) { fromCell = fromRow.getCell(i); newCell = newRow.createCell(i); if (fromCell == null) { continue; } fromCellStyle = fromCell.getCellStyle(); newCell.setCellStyle(fromCellStyle); for (Map.Entry
             
               entry : map.entrySet()) { Object value = entry.getValue(); String fromCellValue = fromCell.getStringCellValue(); if (entry.getKey().equalsIgnoreCase(fromCellValue.split("\\.")[1].trim())) { if (value instanceof Date) { Date date = (Date) value; Calendar cDate = Calendar.getInstance(); cDate.setTime(date); int hh = cDate.get(Calendar.HOUR_OF_DAY); // 获取当前小时 int mm = cDate.get(Calendar.MINUTE); // 获取当前分钟 int ss = cDate.get(Calendar.SECOND); // 获取当前秒 if (hh == 0 && mm == 0 && ss == 0) { newCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd")); } else { newCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd HH:mm:ss")); } } if (value instanceof Integer) { int intValue = (Integer) value; newCell.setCellValue(intValue); } else if (value instanceof Float) { float fValue = (Float) value; if (fromsheet instanceof HSSFSheet) { textValue = new HSSFRichTextString(String.valueOf(fValue)); } if (fromsheet instanceof XSSFSheet) { textValue = new XSSFRichTextString(String.valueOf(fValue)); } newCell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; if (fromsheet instanceof HSSFSheet) { textValue = new HSSFRichTextString(String.valueOf(dValue)); } if (fromsheet instanceof XSSFSheet) { textValue = new XSSFRichTextString(String.valueOf(dValue)); } newCell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; newCell.setCellValue(longValue); } else if (value instanceof String) { newCell.setCellValue(value.toString()); } else if (value instanceof Boolean) { if((boolean) value) { newCell.setCellValue("是"); }else { newCell.setCellValue("否"); } } break; } if(fromCellValue.split("\\.")[1].trim().equals("autoNumber")){ newCell.setCellValue(j); } } } iRow++; } } if (rs != null) { return; } } /** * * @param fromsheet * @param firstrow * @param lastrow * @param data * map
              
               或者javaBean或者ResultSet * @throws Exception */ @SuppressWarnings("unchecked") private static void paddingExcel(Sheet fromsheet, int firstrow, int lastrow, Object data) throws Exception { if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) { return; } Row fromRow = null; Cell fromCell = null; RichTextString textValue = null; if (data != null) { if (data instanceof ResultSet) { return; } else { Map
               
                 map = null; if (data instanceof Map
                ) { map = (Map
                
                 ) data; } else { map = FormatUtil.javaBeanToMap(data); } for (int i = firstrow; i < lastrow; i++) { fromRow = fromsheet.getRow(i); for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) { fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } String fromCellValue = fromCell.getStringCellValue(); if (Strings.isNullOrEmpty(fromCellValue) || (fromCellValue.split("\\.").length < 2)) { continue; } if ("data.start".equalsIgnoreCase(fromCellValue.trim())) { return; } for (Map.Entry
                 
                   entry : map.entrySet()) { Object value = entry.getValue(); if (entry.getKey().equalsIgnoreCase(fromCellValue.split("\\.")[1].trim())) { if (value instanceof Date) { Date date = (Date) value; Calendar cDate = Calendar.getInstance(); cDate.setTime(date); int hh = cDate.get(Calendar.HOUR_OF_DAY); // 获取当前小时 int mm = cDate.get(Calendar.MINUTE); // 获取当前分钟 int ss = cDate.get(Calendar.SECOND); // 获取当前秒 if (hh == 0 && mm == 0 && ss == 0) { fromCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd")); } else { fromCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd HH:mm:ss")); } } if (value instanceof Integer) { int intValue = (Integer) value; fromCell.setCellValue(intValue); } else if (value instanceof Float) { float fValue = (Float) value; if (fromsheet instanceof HSSFSheet) { textValue = new HSSFRichTextString(String.valueOf(fValue)); } if (fromsheet instanceof XSSFSheet) { textValue = new XSSFRichTextString(String.valueOf(fValue)); } fromCell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; if (fromsheet instanceof HSSFSheet) { textValue = new HSSFRichTextString(String.valueOf(dValue)); } if (fromsheet instanceof XSSFSheet) { textValue = new XSSFRichTextString(String.valueOf(dValue)); } fromCell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; fromCell.setCellValue(longValue); } else if (value instanceof String) { fromCell.setCellValue(value.toString()); } else if (value instanceof Boolean) { if((boolean) value) { fromCell.setCellValue("是"); }else { fromCell.setCellValue("否"); } } break; } } } } } } } private static Workbook create(InputStream in) throws Exception { if (!in.markSupported()) { in = new PushbackInputStream(in, 8); } if (POIFSFileSystem.hasPOIFSHeader(in)) { return new HSSFWorkbook(in); } if (POIXMLDocument.hasOOXMLHeader(in)) { return new XSSFWorkbook(OPCPackage.open(in)); } throw new IllegalArgumentException("你的excel版本目前poi解析不了"); } private static void removeRow(Sheet sheet, int rowIndex) { int lastRowNum = sheet.getLastRowNum(); if (rowIndex >= 0 && rowIndex < lastRowNum) { sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } if (rowIndex == lastRowNum) { Row removingRow = sheet.getRow(rowIndex); if (removingRow != null) { sheet.removeRow(removingRow); } } } }
上一篇：JS编写手机接收验证码按钮
下一篇：用JS完成手机短信验证按键点击事件_转载
网友评论
相关栏目
最近更新
热门文章