其中包含两个java类
ExcelUtil.java
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.HSSFRichTextString;
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 {
/************************************XSSF*********************************************/
/**
* 取得指定单元格行和列
* @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 < pos.length; i0++){
if(pos[i0] != null && pos[i0].trim().length() > 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 < numOfSheet; i++){
Sheet sheet = wbPartModule.getSheetAt(i);
templateMap[i] = new HashMap();
readSheet(templateMap[i], sheet);
}
fis.close();
return templateMap;
}
/**
* 读模板数据的样式值置等信息
* @param keyMap
* @param sheet
*/
private static void readSheet(HashMap keyMap, Sheet sheet){
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int j = firstRowNum; j <= lastRowNum; j++) {
Row rowIn = sheet.getRow(j);
if(rowIn == null) {
continue;
}
int firstCellNum = rowIn.getFirstCellNum();
int lastCellNum = rowIn.getLastCellNum();
for (int k = firstCellNum; k <= lastCellNum; k++) {
// Cell cellIn = rowIn.getCell((short) k);
Cell cellIn = rowIn.getCell(k);
if(cellIn == null) {
continue;
}
int cellType = cellIn.getCellType();
if(Cell.CELL_TYPE_STRING != cellType) {
continue;
}
String cellValue = cellIn.getStringCellValue();
if(cellValue == null) {
continue;
}
cellValue = cellValue.trim();
if(cellValue.length() > 2 && cellValue.substring(0,2).equals("<%")) {
String key = cellValue.substring(2, cellValue.length());
String keyPos = Integer.toString(k)+","+Integer.toString(j);
keyMap.put(key, keyPos);
keyMap.put(key+"CellStyle", cellIn.getCellStyle());
} else if(cellValue.length() > 3 && cellValue.substring(0,3).equals("<!%")) {
String key = cellValue.substring(3, cellValue.length());
keyMap.put("STARTCELL", Integer.toString(j));
keyMap.put(key, Integer.toString(k));
keyMap.put(key+"CellStyle", cellIn.getCellStyle());
}
}
}
}
/**
* 获取格式，不适于循环方法中使用，wb.createCellStyle()次数超过4000将抛异常
* @param keyMap
* @param key
* @return
*/
public static CellStyle getStyle(HashMap keyMap, String key,Workbook wb) {
CellStyle cellStyle = null;
cellStyle = (CellStyle) keyMap.get(key+"CellStyle");
//当字符超出时换行
cellStyle.setWrapText(true);
CellStyle newStyle = wb.createCellStyle();
newStyle.cloneStyleFrom(cellStyle);
return newStyle;
}
/**
* Excel单元格输出
* @param sheet
* @param row 行
* @param cell 列
* @param value 值
* @param cellStyle 样式
*/
public static void setValue(Sheet sheet, int row, int cell, Object value, CellStyle cellStyle){
Row rowIn = sheet.getRow(row);
if(rowIn == null) {
rowIn = sheet.createRow(row);
}
Cell cellIn = rowIn.getCell(cell);
if(cellIn == null) {
cellIn = rowIn.createCell(cell);
}
if(cellStyle != null) {
//修复产生多超过4000 cellStyle 异常
//CellStyle newStyle = wb.createCellStyle();
//newStyle.cloneStyleFrom(cellStyle);
cellIn.setCellStyle(cellStyle);
}
//对时间格式进行单独处理
if(value==null){
cellIn.setCellValue("");
}else{
if (isCellDateFormatted(cellStyle)) {
cellIn.setCellValue((Date) value);
} else {
// cellIn.setCellValue(new XSSFRichTextString(value.toString()));
try {
cellIn.setCellValue(new HSSFRichTextString(value.toString()));
} catch (Exception e) {
cellIn.setCellValue(new XSSFRichTextString(value.toString()));
}
}
}
}
/**
* 根据表格样式判断是否为日期格式
* @param cellStyle
* @return
*/
public static boolean isCellDateFormatted(CellStyle cellStyle){
if(cellStyle==null){
return false;
}
int i = cellStyle.getDataFormat();
String f = cellStyle.getDataFormatString();
return org.apache.poi.ss.usermodel.DateUtil.isADateFormat(i, f);
}
/**
* 适用于导出的数据Excel格式样式重复性较少
* 不适用于循环方法中使用
* @param wbModule
* @param sheet
* @param pos 模板文件信息
* @param startCell 开始的行
* @param value 要填充的数据
* @param cellStyle 表格样式
*/
public static void createCell(Workbook wbModule, Sheet sheet,HashMap pos, int startCell,Object value,String cellStyle){
int[] excelPos = getPos(pos, cellStyle);
setValue(sheet, startCell, excelPos[0], value, getStyle(pos, cellStyle,wbModule));
}
/************************************XSSF*******************************************/
}
还有这个主要的ExcelHandle.java
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 org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
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;
/**
* 对excel进行操作工具类
*
**/
@SuppressWarnings("rawtypes")
public class ExcelHandle {
private Map<String,HashMap[]> tempFileMap = new HashMap<String,HashMap[]>();
private Map<String,Map<String,Cell>> cellMap = new HashMap<String,Map<String,Cell>>();
private Map<String,FileInputStream> tempStream = new HashMap<String, FileInputStream>();
private Map<String,Workbook> tempWorkbook = new HashMap<String, Workbook>();
private Map<String,Workbook> dataWorkbook = new HashMap<String, Workbook>();
/**
* 单无格类
* @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 需要填充的数据（模板<!%后的字符串）
* @param dataList 填充的数据
* @param sheet 填充的excel sheet,从0开始
* @throws IOException
*/
public void writeListData(String tempFilePath,List<String> cellList,List<Map<String,Object>> 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);
//移除模板开始行数据即<!%
wsheet.removeRow(wsheet.getRow(startCell));
if(dataList!=null&&dataList.size()>0){
for(Map<String,Object> map:dataList){
//填完一行新建一行
wsheet.shiftRows(startCell+1, wsheet.getLastRowNum(), 1,true,false);
Row srow = wsheet.createRow(startCell+1);
String celNum = "";
for(String cell:cellList){
//获取对应单元格数据
Cell c = getCell(cell,temp,temWorkbook,tempFilePath);
// srow.createCell(c.getColumn()).setCellStyle(c.getCellStyle());
setCelStyle(srow, c.getColumn(), c.getCellStyle());
celNum += c.getColumn()+",";
//写入数据
ExcelUtil.setValue(wsheet, startCell, c.getColumn(), map.get(cell), c.getCellStyle());
}
startCell++;
mergedRegion(wsheet,startCell,celNum);
}
}
}
/**
* 合并列单元格
* @param wsheet
* @param row
* @param celNum
*/
public void mergedRegion(Sheet wsheet, int row, String celNum){
celNum = celNum.substring(0, celNum.length()-1);
String str[] = celNum.split(",");
for(int i = 0; i < str.length; i++){
if(i != 0){
wsheet.addMergedRegion(new CellRangeAddress(row, row, Integer.parseInt(str[i-1]), Integer.parseInt(str[i])-1));
}
}
}
/**
* 设置单元格样式
* @param srow
* @param column
* @param c
*/
public void setCelStyle(Row srow, int column, CellStyle cs){
for(int i = 0; i < column; i++){
srow.createCell(i).setCellStyle(cs);
}
}
/**
* 按模板向Excel中相应地方填充数据
* @param tempFilePath excel模板文件路径
* @param cellList 需要填充的数据（模板<%后的字符串）
* @param dataMap 填充的数据
* @param sheet 填充的excel sheet,从0开始
* @throws IOException
*/
public void writeData(String tempFilePath,List<String> cellList,Map<String,Object> dataMap,int sheet) throws IOException{
//获取模板填充格式位置等数据
HashMap tem = getTemp(tempFilePath,sheet);
//按模板为写入板
Workbook wbModule = getTempWorkbook(tempFilePath);
//数据填充的sheet
Sheet wsheet = wbModule.getSheetAt(sheet);
if(dataMap!=null&&dataMap.size()>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<Map<String,Object>> getListValue(String tempFilePath,List<String> cellList,int sheet,File excelFile) throws IOException{
List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
//获取模板填充格式位置等数据
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;i<=lastLine;i++){
dataList.add(getListLineValue(i, tempFilePath, cellList, sheet, excelFile));
}
return dataList;
}
/**
* 读值一行列表值
* @param tempFilePath
* @param cell
* @param sheet
* @return
* @throws IOException
*/
public Map<String,Object> getListLineValue(int line,String tempFilePath,List<String> cellList,int sheet,File excelFile) throws IOException{
Map<String,Object> lineMap = new HashMap<String, Object>();
//获取模板填充格式位置等数据
HashMap tem = getTemp(tempFilePath,sheet);
//按模板为写入板
Workbook temWorkbook = getTempWorkbook(tempFilePath);
//将Excel文件转换为工作区间
Workbook dataWorkbook = getDataWorkbook(tempFilePath,excelFile) ;
//数据sheet
Sheet dataSheet = dataWorkbook.getSheetAt(sheet);
for(String cell:cellList){
//获取对应单元格数据
Cell c = getCell(cell,tem,temWorkbook,tempFilePath);
lineMap.put(cell, ExcelUtil.getCellValue(dataSheet, line, c.getColumn()));
}
return lineMap;
}
/**
* 获得模板输入流
* @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);
}
/**
* 获得输入工作区
* @param tempFilePath
* @return
* @throws IOException
* @throws FileNotFoundException
*/
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);
}
/**
* 获取对应单元格样式等数据数据
* @param cell
* @param tem
* @param wbModule
* @param tempFilePath
* @return
*/
private Cell getCell(String cell, HashMap tem, Workbook wbModule, String tempFilePath) {
if(!cellMap.get(tempFilePath).containsKey(cell)){
Cell c = new Cell();
int[] pos = ExcelUtil.getPos(tem, cell);
if(pos.length>1){
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<String,Cell>());
}
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("D:/workspace-m-c/reportExcelPoi/src/template.xls").getPath();
String tempFilePath = "D:/workspace-m-c/reportExcelPoi/src/template.xls";
List<String> dataListCell = new ArrayList<String>();
dataListCell.add("date");
dataListCell.add("name");
dataListCell.add("cls");
dataListCell.add("sj");
dataListCell.add("type");
List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
for(int i = 0; i < 10; i++){
Map eduMap = new HashMap();
eduMap.put("date", "2016-02-01"+i);
eduMap.put("name", "啊大发光火教育机构11");
eduMap.put("cls", "Oracle11");
eduMap.put("sj", "本科11");
eduMap.put("type", "在职11");
dataList.add(eduMap);
}
// Map eduMap = new HashMap();
// eduMap.put("date", "2016-02-01");
// eduMap.put("name", "啊大发光火教育机构11");
// eduMap.put("cls", "Oracle11");
// eduMap.put("sj", "本科11");
// eduMap.put("type", "在职11");
// dataList.add(eduMap);
// Map eduMap2 = new HashMap();
// eduMap2.put("date", "2016-02-02");
// eduMap2.put("name", "啊大发光火教育机构22");
// eduMap2.put("cls", "Oracle22");
// eduMap2.put("sj", "本科22");
// eduMap2.put("type", "在职22");
// dataList.add(eduMap2);
ExcelHandle handle = new ExcelHandle();
handle.writeListData(tempFilePath, dataListCell, dataList, 0);
List<String> dataCell = new ArrayList<String>();
dataCell.add("names");
dataCell.add("sex");
dataCell.add("des");
Map<String,Object> dataMap = new HashMap<String, Object>();
dataMap.put("names", "张三");
dataMap.put("sex", "女");
dataMap.put("des", "测试");
handle.writeData(tempFilePath, dataCell, dataMap, 0);
File file = new File("d:/data2.xls");
OutputStream os = new FileOutputStream(file);
//写到输出流并关闭资源
handle.writeAndClose(tempFilePath, os);
os.flush();
os.close();
System.out.println("读取写入的数据----------------------------------%%%");
System.out.println("name:"+handle.getValue(tempFilePath, "names", 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<Map<String,Object>> list = handle.getListValue(tempFilePath, dataListCell, 0, file);
// for(Map<String,Object> data:list){
// for(String key:data.keySet()){
// System.out.print(key+":"+data.get(key)+"--");
// }
// System.out.println("");
// }
handle.readClose(tempFilePath);
}
}
使用的模板内容
使用的jar包
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
