poi根據(jù)模板導(dǎo)出excel

2019-11-06 06:14:02

字體：大中小

供稿：網(wǎng)友

package com.CSS.commcon.util;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;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.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil {//模板map PRivate Map<String,Workbook> tempWorkbook = new HashMap<String, Workbook>(); //模板輸入流map private Map<String,FileInputStream> tempStream = new HashMap<String, FileInputStream>(); /** * * * 描述：臨時(shí)單元格數(shù)據(jù) * * @ClassName: Cell * @version V1.0 * @date 2015年9月26日 * @author <a href="mailto:1435290472@QQ.com">zq</a> */ class TempCell{ private int row; private int column; private CellStyle cellStyle; private Object data; //用于列表合并，表示幾列合并 private int columnSize = -1; public int getColumn() { return column; } public void setColumn(int column) { this.column = column; } public int getRow() { return row; } public void setRow(int row) { this.row = row; } public CellStyle getCellStyle() { return cellStyle; } public void setCellStyle(CellStyle cellStyle) { this.cellStyle = cellStyle; } public Object getData() { return data; } public void setData(Object data) { this.data = data; } public int getColumnSize() { return columnSize; } public void setColumnSize(int columnSize) { this.columnSize = columnSize; } } /** * * * 功能：按模板向Excel中相應(yīng)地方填充數(shù)據(jù) * * @date 2015年9月26日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempFilePath * @param dataMap * @param sheetNo * @throws IOException */ public void writeData(String tempFilePath,Map<String,Object> dataMap,int sheetNo) throws IOException{ //獲取模板填充格式位置等數(shù)據(jù) // HashMap tem = getTemp(tempFilePath,sheet); //讀取模板 Workbook wbModule = getTempWorkbook(tempFilePath); //數(shù)據(jù)填充的sheet Sheet wsheet = wbModule.getSheetAt(sheetNo); Iterator it = dataMap.entrySet().iterator(); while(it.hasNext()){ @SuppressWarnings("unchecked")Entry<String, Object> entry = (Entry<String, Object>) it.next(); String point = entry.getKey(); Object data = entry.getValue(); TempCell cell = getCell(point, data,wsheet); //指定坐標(biāo)賦值 setCell(cell,wsheet); } //設(shè)置生成excel中公式自動(dòng)計(jì)算 wsheet.setForceFormulaRecalculation(true); } /** * * * 功能：按模板向Excel中列表填充數(shù)據(jù)。只支持列合并 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempFilePath * @param heads 列表頭部位置集合 * @param datalist * @param sheetNo * @throws FileNotFoundException * @throws IOException */ public void writeDateList(String tempFilePath,String[] heads,List<Map<Integer, Object>> datalist,int sheetNo) throws FileNotFoundException, IOException { //讀取模板 Workbook wbModule = getTempWorkbook(tempFilePath); //數(shù)據(jù)填充的sheet Sheet wsheet = wbModule.getSheetAt(sheetNo); //列表數(shù)據(jù)模板cell List<TempCell> tempCells = new ArrayList<TempCell>(); for(int i=0;i<heads.length;i++){ String point = heads[i]; TempCell tempCell = getCell(point,null,wsheet); //取得合并單元格位置 -1：表示不是合并單元格 int pos = isMergedRegion(wsheet, tempCell.getRow(), tempCell.getColumn()); if(pos>-1){ CellRangeAddress range = wsheet.getMergedRegion(pos); tempCell.setColumnSize(range.getLastColumn()-range.getFirstColumn()); } tempCells.add(tempCell); } //賦值 for(int i=0;i<datalist.size();i++){ Map<Integer, Object> dataMap = datalist.get(i); for(int j=0;j<tempCells.size();j++){ TempCell tempCell = tempCells.get(j); tempCell.setRow(tempCell.getRow()+1); tempCell.setData(dataMap.get(j+1)); setCell(tempCell, wsheet); } } } /** * * * 功能：獲取輸入工作區(qū) * * @date 2015年9月26日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempFilePath * @return * @throws FileNotFoundException * @throws IOException */ private Workbook getTempWorkbook(String tempFilePath) throws FileNotFoundException, IOException { if(!tempWorkbook.containsKey(tempFilePath)){ if(tempFilePath.endsWith(".xlsx")){ tempWorkbook.put(tempFilePath, new XSSFWorkbook(getFileInputStream(tempFilePath))); }else if(tempFilePath.endsWith(".xls")){ tempWorkbook.put(tempFilePath, new HSSFWorkbook(getFileInputStream(tempFilePath))); } } return tempWorkbook.get(tempFilePath); } /** * * * 功能：獲得模板輸入流 * * @date 2015年9月26日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempFilePath * @return * @throws FileNotFoundException */ private FileInputStream getFileInputStream(String tempFilePath) throws FileNotFoundException { if(!tempStream.containsKey(tempFilePath)){ tempStream.put(tempFilePath, new FileInputStream(tempFilePath)); } return tempStream.get(tempFilePath); } /** * * * 功能：設(shè)置單元格數(shù)據(jù),樣式 (根據(jù)坐標(biāo)：B3) * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param point * @param data * @param sheet * @return */ private TempCell getCell(String point,Object data,Sheet sheet){ TempCell tempCell = new TempCell(); //得到列字母 String lineStr = ""; String reg = "[A-Z]+"; Pattern p = Pattern.compile(reg); Matcher m = p.matcher(point); while(m.find()){ lineStr = m.group(); } //將列字母轉(zhuǎn)成列號根據(jù)ascii轉(zhuǎn)換 char[] ch = lineStr.toCharArray(); int column = 0; for(int i=0;i<ch.length;i++){ char c = ch[i]; int post = ch.length-i-1; int r = (int) Math.pow(10, post); column = column + r*((int)c-65); } tempCell.setColumn(column); //得到行號 reg = "[1-9]+"; p = Pattern.compile(reg); m = p.matcher(point); while(m.find()){ tempCell.setRow((Integer.parseInt(m.group())-1)); } //獲取模板指定單元格樣式，設(shè)置到tempCell （寫列表數(shù)據(jù)的時(shí)候用） Row rowIn = sheet.getRow(tempCell.getRow()); if(rowIn == null) { rowIn = sheet.createRow(tempCell.getRow()); } Cell cellIn = rowIn.getCell(tempCell.getColumn()); if(cellIn == null) { cellIn = rowIn.createCell(tempCell.getColumn()); } tempCell.setCellStyle(cellIn.getCellStyle()); tempCell.setData(data); return tempCell; } /** * * * 功能：給指定坐標(biāo)賦值 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempCell * @param sheet */ private void setCell(TempCell tempCell,Sheet sheet) { if(tempCell.getColumnSize()>-1){ CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn()+tempCell.getColumnSize()); setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet); } Row rowIn = sheet.getRow(tempCell.getRow()); if(rowIn == null) { rowIn = sheet.createRow(tempCell.getRow()); } Cell cellIn = rowIn.getCell(tempCell.getColumn()); if(cellIn == null) { cellIn = rowIn.createCell(tempCell.getColumn()); } //根據(jù)data類型給cell賦值 if(tempCell.getData() instanceof String){ cellIn.setCellValue((String)tempCell.getData()); }else if(tempCell.getData() instanceof Integer){ cellIn.setCellValue((int)tempCell.getData()); }else if(tempCell.getData() instanceof Double){ cellIn.setCellValue((double)tempCell.getData()); }else{ cellIn.setCellValue((String)tempCell.getData()); } //樣式 if(tempCell.getCellStyle()!=null && tempCell.getColumnSize()==-1){ cellIn.setCellStyle(tempCell.getCellStyle()); } } /** * * * 功能：寫到輸出流并移除資源 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param tempFilePath * @param os * @throws FileNotFoundException * @throws IOException */ public void writeAndClose(String tempFilePath,OutputStream os) throws FileNotFoundException, IOException{ if(getTempWorkbook(tempFilePath)!=null){ getTempWorkbook(tempFilePath).write(os); tempWorkbook.remove(tempFilePath); } if(getFileInputStream(tempFilePath)!=null){ getFileInputStream(tempFilePath).close(); tempStream.remove(tempFilePath); } } /** * * * 功能：判斷指定的單元格是否是合并單元格 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param sheet * @param row * @param column * @return 0:不是合并單元格，i:合并單元格的位置 */ private Integer isMergedRegion(Sheet sheet,int row ,int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ return i; } } } return -1; } /** * * * 功能：合并單元格 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param sheet * @param firstRow * @param lastRow * @param firstCol * @param lastCol */ private CellRangeAddress mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { CellRangeAddress rang = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(rang); return rang; } /** * * * 功能：設(shè)置合并單元格樣式 * * @date 2015年9月27日 * @author <a href="mailto:1435290472@qq.com">zq</a> * @param cs * @param region * @param sheet */ private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet){ for(int i=region.getFirstRow();i<=region.getLastRow();i++){ Row row=sheet.getRow(i); if(row==null) row=sheet.createRow(i); for(int j=region.getFirstColumn();j<=region.getLastColumn();j++){ Cell cell=row.getCell(j); if(cell==null){ cell=row.createCell(j); cell.setCellValue(""); } cell.setCellStyle(cs); } } } public static void main(String[] args) throws FileNotFoundException, IOException {// String tempFilePath = ExcelUtil.class.getResource("demo.xlsx").getPath(); String tempFilePath = "D:/demo.xlsx"; File file = new File("d:/data.xlsx"); OutputStream os = new FileOutputStream(file); ExcelUtil excel = new ExcelUtil();// Map<String, Object> dataMap = new HashMap<String, Object>();// dataMap.put("B1", "dddd");// dataMap.put("B2", 55);// dataMap.put("B3", 55);// excel.writeData(tempFilePath, dataMap, 0); List<Map<Integer, Object>> datalist = new ArrayList<Map<Integer,Object>>(); Map<Integer, Object> data = new HashMap<Integer,Object>(); data.put(1, "雷濤"); data.put(2, "男"); data.put(3, 4222); datalist.add(data); data = new HashMap<Integer,Object>(); data.put(1, "小花"); data.put(2, "女"); data.put(3, "5555555"); datalist.add(data); data = new HashMap<Integer,Object>(); data.put(1, "小華"); data.put(2, "女"); data.put(3, "66665"); datalist.add(data); String[] heads = new String[]{"A1","B1","C1"}; //必須為列表頭部所有位置集合，輸出數(shù)據(jù)單元格樣式和頭部單元格樣式保持一致 excel.writeDateList(tempFilePath,heads,datalist,0); //寫到輸出流并移除資源 excel.writeAndClose(tempFilePath, os); System.out.println("導(dǎo)出成功"); os.flush(); os.close(); }}

上一篇：MyEclipse進(jìn)入debug模式，f6按鍵不起作用

下一篇：TreeSet的定制排序