| POI按模板导入导出EXCEL工具类 | 您所在的位置：网站首页 › poi读取模板导出excel › POI按模板导入导出EXCEL工具类 |
POI按模板导入导出EXCEL工具类
|
一、Excel封装类
package com.xx.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
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.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@SuppressWarnings({"rawtypes","unchecked"})
public class ExcelUtil {
/**
* 取得指定单元格行和列
* @param keyMap 所有单元格行、列集合
* @param key 单元格标识
* @return 0：列 1：行（列表型数据不记行，即1无值）
*/
public static int[] getPos(HashMap keyMap, String key){
int[] ret = new int[0];
String val = (String)keyMap.get(key);
if(val == null || val.length() == 0)
return ret;
String pos[] = val.split(",");
if(pos.length == 1 || pos.length == 2){
ret = new int[pos.length];
for(int i0 = 0; i0 0){
ret[i0] = Integer.parseInt(pos[i0].trim());
} else {
ret[i0] = 0;
}
}
}
return ret;
}
/**
* 取对应格子的值
* @param sheet
* @param rowNo 行
* @param cellNo 列
* @return
* @throws IOException
*/
public static String getCellValue(Sheet sheet,int rowNo,int cellNo) {
String cellValue = null;
Row row = sheet.getRow(rowNo);
Cell cell = row.getCell(cellNo);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DecimalFormat df = new DecimalFormat("0");
cellValue = getCutDotStr(df.format(cell.getNumericCellValue()));
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
cellValue = cell.getStringCellValue();
}
if (cellValue != null) {
cellValue = cellValue.trim();
}
} else {
cellValue = null;
}
return cellValue;
}
/**
* 取整数
* @param srcString
* @return
*/
private static String getCutDotStr(String srcString) {
String newString = "";
if (srcString != null && srcString.endsWith(".0")) {
newString = srcString.substring(0,srcString.length()-2);
} else {
newString = srcString;
}
return newString;
}
/**
* 读数据模板
* @param 模板地址
* @throws IOException
*/
public static HashMap[] getTemplateFile(String templateFileName) throws IOException {
FileInputStream fis = new FileInputStream(templateFileName);
Workbook wbPartModule = null;
if(templateFileName.endsWith(".xlsx")){
wbPartModule = new XSSFWorkbook(fis);
}else if(templateFileName.endsWith(".xls")){
wbPartModule = new HSSFWorkbook(fis);
}
int numOfSheet = wbPartModule.getNumberOfSheets();
HashMap[] templateMap = new HashMap[numOfSheet];
for(int i = 0; i
二、操作数据助手类
package com.XX.utils;
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.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 对excel进行操作工具类
*@author xiliang.xiao
*@date 2015年1月8日 下午1:46:36
*
**/
@SuppressWarnings("rawtypes")
public class ExcelHandle {
private Map tempFileMap = new HashMap();
private Map cellMap = new HashMap();
private Map tempStream = new HashMap();
private Map tempWorkbook = new HashMap();
private Map dataWorkbook = new HashMap();
/**
* 单无格类
* @author xiliang.xiao
*
*/
class Cell{
private int column;//列
private int line;//行
private CellStyle cellStyle;
public int getColumn() {
return column;
}
public void setColumn(int column) {
this.column = column;
}
public int getLine() {
return line;
}
public void setLine(int line) {
this.line = line;
}
public CellStyle getCellStyle() {
return cellStyle;
}
public void setCellStyle(CellStyle cellStyle) {
this.cellStyle = cellStyle;
}
}
/**
* 向Excel中输入相同title的多条数据
* @param tempFilePath excel模板文件路径
* @param cellList 需要填充的数据（模板 cellList,List dataList,int sheet) throws IOException{
//获取模板填充格式位置等数据
HashMap temp = getTemp(tempFilePath,sheet);
//按模板为写入板
Workbook temWorkbook = getTempWorkbook(tempFilePath);
//获取数据填充开始行
int startCell = Integer.parseInt((String)temp.get("STARTCELL"));
//数据填充的sheet
Sheet wsheet = temWorkbook.getSheetAt(sheet);
//移除模板开始行数据即0){
for(Map map:dataList){
for(String cell:cellList){
//获取对应单元格数据
Cell c = getCell(cell,temp,temWorkbook,tempFilePath);
//写入数据
ExcelUtil.setValue(wsheet, startCell, c.getColumn(), map.get(cell), c.getCellStyle());
}
startCell++;
}
}
}
/**
* 按模板向Excel中相应地方填充数据
* @param tempFilePath excel模板文件路径
* @param cellList 需要填充的数据（模板0){
for(String cell:cellList){
//获取对应单元格数据
Cell c = getCell(cell,tem,wbModule,tempFilePath);
ExcelUtil.setValue(wsheet, c.getLine(), c.getColumn(), dataMap.get(cell), c.getCellStyle());
}
}
}
/**
* Excel文件读值
* @param tempFilePath
* @param cell
* @param sheet
* @return
* @throws IOException
*/
public Object getValue(String tempFilePath,String cell,int sheet,File excelFile) throws IOException{
//获取模板填充格式位置等数据
HashMap tem = getTemp(tempFilePath,sheet);
//模板工作区
Workbook temWorkbook = getTempWorkbook(tempFilePath);
//数据工作区
Workbook dataWorkbook = getDataWorkbook(tempFilePath, excelFile);
//获取对应单元格数据
Cell c = getCell(cell,tem,temWorkbook,tempFilePath);
//数据sheet
Sheet dataSheet = dataWorkbook.getSheetAt(sheet);
return ExcelUtil.getCellValue(dataSheet, c.getLine(), c.getColumn());
}
/**
* 读值列表值
* @param tempFilePath
* @param cell
* @param sheet
* @return
* @throws IOException
*/
public List getListValue(String tempFilePath,List cellList,int sheet,File excelFile) throws IOException{
List dataList = new ArrayList();
//获取模板填充格式位置等数据
HashMap tem = getTemp(tempFilePath,sheet);
//获取数据填充开始行
int startCell = Integer.parseInt((String)tem.get("STARTCELL"));
//将Excel文件转换为工作区间
Workbook dataWorkbook = getDataWorkbook(tempFilePath,excelFile) ;
//数据sheet
Sheet dataSheet = dataWorkbook.getSheetAt(sheet);
//文件最后一行
int lastLine = dataSheet.getLastRowNum();
for(int i=startCell;i1){
c.setLine(pos[1]);
}
c.setColumn(pos[0]);
c.setCellStyle((ExcelUtil.getStyle(tem, cell, wbModule)));
cellMap.get(tempFilePath).put(cell, c);
}
return cellMap.get(tempFilePath).get(cell);
}
/**
* 获取模板数据
* @param tempFilePath 模板文件路径
* @param sheet
* @return
* @throws IOException
*/
private HashMap getTemp(String tempFilePath, int sheet) throws IOException {
if(!tempFileMap.containsKey(tempFilePath)){
tempFileMap.put(tempFilePath, ExcelUtil.getTemplateFile(tempFilePath));
cellMap.put(tempFilePath, new HashMap());
}
return tempFileMap.get(tempFilePath)[sheet];
}
/**
* 资源关闭
* @param tempFilePath 模板文件路径
* @param os 输出流
* @throws IOException
* @throws FileNotFoundException
*/
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);
}
}
/**
* 获得读取数据工作间
* @param tempFilePath
* @param excelFile
* @return
* @throws IOException
* @throws FileNotFoundException
*/
private Workbook getDataWorkbook(String tempFilePath, File excelFile) throws FileNotFoundException, IOException {
if(!dataWorkbook.containsKey(tempFilePath)){
if(tempFilePath.endsWith(".xlsx")){
dataWorkbook.put(tempFilePath, new XSSFWorkbook(new FileInputStream(excelFile)));
}else if(tempFilePath.endsWith(".xls")){
dataWorkbook.put(tempFilePath, new HSSFWorkbook(new FileInputStream(excelFile)));
}
}
return dataWorkbook.get(tempFilePath);
}
/**
* 读取数据后关闭
* @param tempFilePath
*/
public void readClose(String tempFilePath){
dataWorkbook.remove(tempFilePath);
}
public static void main(String args[]) throws IOException{
String tempFilePath = ExcelHandle.class.getResource("test.xlsx").getPath();
List dataListCell = new ArrayList();
dataListCell.add("names");
dataListCell.add("ages");
dataListCell.add("sexs");
dataListCell.add("deses");
List dataList = new ArrayList();
Map map = new HashMap();
map.put("names", "names");
map.put("ages", 22);
map.put("sexs", "男");
map.put("deses", "测试");
dataList.add(map);
Map map1 = new HashMap();
map1.put("names", "names1");
map1.put("ages", 23);
map1.put("sexs", "男");
map1.put("deses", "测试1");
dataList.add(map1);
Map map2 = new HashMap();
map2.put("names", "names2");
map2.put("ages", 24);
map2.put("sexs", "女");
map2.put("deses", "测试2");
dataList.add(map2);
Map map3 = new HashMap();
map3.put("names", "names3");
map3.put("ages", 25);
map3.put("sexs", "男");
map3.put("deses", "测试3");
dataList.add(map3);
ExcelHandle handle = new ExcelHandle();
handle.writeListData(tempFilePath, dataListCell, dataList, 0);
List dataCell = new ArrayList();
dataCell.add("name");
dataCell.add("age");
dataCell.add("sex");
dataCell.add("des");
Map dataMap = new HashMap();
dataMap.put("name", "name");
dataMap.put("age", 11);
dataMap.put("sex", "女");
dataMap.put("des", "测试");
handle.writeData(tempFilePath, dataCell, dataMap, 0);
File file = new File("d:/data.xlsx");
OutputStream os = new FileOutputStream(file);
//写到输出流并关闭资源
handle.writeAndClose(tempFilePath, os);
os.flush();
os.close();
System.out.println("读取写入的数据----------------------------------%%%");
System.out.println("name:"+handle.getValue(tempFilePath, "name", 0, file));
System.out.println("age:"+handle.getValue(tempFilePath, "age", 0, file));
System.out.println("sex:"+handle.getValue(tempFilePath, "sex", 0, file));
System.out.println("des:"+handle.getValue(tempFilePath, "des", 0, file));
System.out.println("读取写入的列表数据----------------------------------%%%");
List list = handle.getListValue(tempFilePath, dataListCell, 0, file);
for(Map data:list){
for(String key:data.keySet()){
System.out.print(key+":"+data.get(key)+"--");
}
System.out.println("");
}
handle.readClose(tempFilePath);
}
}
三、模板截图
读取写入的数据----------------------------------%%% name:name age:11 sex:女 des:测试 读取写入的列表数据----------------------------------%%% names:names--deses:测试--sexs:男--ages:22-- names:names1--deses:测试1--sexs:男--ages:23-- names:names2--deses:测试2--sexs:女--ages:24-- names:names3--deses:测试3--sexs:男--ages:25-- |
【本文地址】
公司简介
联系我们
| 今日新闻 |
| 推荐新闻 |
| 专题文章 |