学会简单使用poi进行excel有关操作

package com.test;import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
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.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;/**
* 根据api一些简单操作
* @author Administrator
*
*/
public class CreateExcel {
String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook";
public static void main(String[] args) {
CreateExcel ce=new CreateExcel();
// String str="C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook";
// ce.createSheet(new HSSFWorkbook(), str+"\\demo0.xls");
// ce.createCell();
ce.createDateCell();
} /**
* 创建Workbook
*/
public void createWorkbook(){ Workbook wb=null;
FileOutputStream fileOut=null;
try {
wb=new HSSFWorkbook();
fileOut=new FileOutputStream(str+"\\demo.xls");
wb.write(fileOut);
fileOut.close(); wb=new XSSFWorkbook();
fileOut=new FileOutputStream(str+"\\demo1.xlsx");
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
} //建表，不要问我为什么注释有些是英文的，官网上扣下来的
public void createSheet(Workbook wb,String filename){
Sheet sheet1=wb.createSheet("sheet1");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (]) // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ') String safeName=WorkbookUtil.createSafeSheetName("[O'Briend's sales*?]");//这个方法会去除名称字符串中的不合法字符
Sheet sheet3=wb.createSheet(safeName);
try {
FileOutputStream fileOut=new FileOutputStream(filename, true);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} /**
* 创建细胞，也就是创建格子
*/
public void createCell(){
Workbook wb=new HSSFWorkbook();//xsl
//Workbook wb=new XSSFWorkbook();
CreationHelper createHelper=wb.getCreationHelper();
Sheet sheet=wb.createSheet("sheet3"); //create a row and put some cells in it.Rows are 0 based
Row row=sheet.createRow((short)0);
//create a cell and put a value in it
Cell cell=row.createCell(0);//不填写类型的话，默认的类型是this.createCell(column,HSSFCell.CELL_TYPE_BLANK);
cell.setCellValue(1); //or do it on one line
row.createCell(1).setCellValue(1.2);//创建和赋值连成一行
row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue("nihao"); //write the output to a file
try { FileOutputStream fileOut=new FileOutputStream("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\demo.xls",true);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //创建日期类型cell
public void createDateCell(){
Workbook wb=new HSSFWorkbook();
//Workbook wb=new XSSFWorkbook();
CreationHelper createHelper=wb.getCreationHelper();
Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(0);
//create a row and put a date value in it.The first cell is not styled as a date.
Cell cell=row.createCell(0);
cell.setCellValue(new Date()); //we style the second cell as a date (and time).It is important to create a new
//cell style from the workbook oterwise you can end up modifying the builting in style and effecting not only this cell but other cells
//设置日期的格式是分厂重要的
CellStyle cellStyle=wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("m/d/yy h:mm")
) ;
cell=row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle); //write the output to a file
FileOutputStream fileOut;
try { fileOut = new FileOutputStream(str+"\\demo4.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} } //使用不同类型的cell
@Test
public void useOtherTypeCell(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("new sheet");
Row row=sheet.createRow((short)2);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(new Date());
row.createCell(2).setCellValue(Calendar.getInstance());
row.createCell(3).setCellValue("a string");
row.createCell(4).setCellValue(true);
row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR); //write the output to a file
FileOutputStream fileOut;
try { fileOut = new FileOutputStream(str+"\\demo5.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //文件VS输入流
@Test
public void FileVSStream(){
//打开工作薄时，要么.xsl HSSFWorkbook或.xlsx XSSFWorkbook,可以从一个工作薄 文件 或者一个InputStream，使用一个文件对象允许更低的
//内存消耗，而一个InputStream需要更多的内存缓冲区 //使用WorkbookFactory获取Workbook
try {
Workbook wb=WorkbookFactory.create(new File(str+"\\MyExcel.xlsx"));
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} //使用InputStream获取Workbook对象
try {
Workbook wb=WorkbookFactory.create(new FileInputStream(str+"\\MyExcel.xlsx")); } catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} //如果使用HSSFWorkbook或者XSSFWorkbook直接，通常应该使用NPOIFSFileSystem或OPCPackage 有完全控制的生命周期（包括完成后关闭该文件）
try {
NPOIFSFileSystem fs=new NPOIFSFileSystem(new File(str+"\\MyExcel.xls"));
HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true);
//...
fs.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
NPOIFSFileSystem fs=new NPOIFSFileSystem(new FileInputStream(str+"\\MyExcel.xls"));
HSSFWorkbook wb=new HSSFWorkbook(fs.getRoot(),true);
//...
fs.close();
} catch (IOException e) {
e.printStackTrace();
}
//XXSFWorkbook
try {
OPCPackage pkg=OPCPackage.open(new File(str+"\\MyExcel.xlsx"));
XSSFWorkbook wb=new XSSFWorkbook(pkg);
//...
pkg.close();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
OPCPackage pkg=OPCPackage.open(new FileInputStream(str+"\\MyExcel.xlsx"));
XSSFWorkbook wb=new XSSFWorkbook(pkg);
//...
pkg.close();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //演示各种对齐选项
@Test
public void alignTest(){
Workbook wb=new XSSFWorkbook(); Sheet sheet=wb.createSheet();
Row row=sheet.createRow((short)2);
row.setHeightInPoints(30);//设置行高 createCell(wb,row,(short)0,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_BOTTOM);
createCell(wb,row,(short)1,CellStyle.ALIGN_CENTER_SELECTION,CellStyle.VERTICAL_BOTTOM);
createCell(wb,row,(short)2,CellStyle.ALIGN_FILL,CellStyle.VERTICAL_CENTER);
createCell(wb,row,(short)3,CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP); //write the output to a file
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\xssf-align.xlsx");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} }
public void createCell(Workbook wb,Row row,short column,short halign,short valign){
Cell cell=row.createCell(column);
cell.setCellValue("Align It");
CellStyle cellStyle=wb.createCellStyle();
cellStyle.setAlignment(halign);
cellStyle.setVerticalAlignment(valign);
cell.setCellStyle(cellStyle);
} //使用边界
@Test
public void usrBorder(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(1); Cell cell=row.createCell(1);
cell.setCellValue(4); //style the cell with borders all around
CellStyle style=wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo7.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} } @Test
public void IteratorRowAndCell(){
//有时候，你想遍历workbook，一个表中的所有行或者所有的cell
/*
*workbook.sheetIterator(),sheet.rowIterator(),row.cellIterator() ,或者隐式地使用forEach
*注意使用迭代器等遍历注意间隔和空缝隙
*/
} public void IteratorWhiteCell(Sheet sheet){
int rowStart=Math.min(15,sheet.getFirstRowNum());
int rowEnd=Math.max(1400, sheet.getLastRowNum()); for(int rowNum=rowStart;rowNum<rowEnd;rowNum++){
Row r=sheet.getRow(rowNum);
if(r==null){
//这行是空的
continue;
}
int lastColumn=r.getLastCellNum();
for(int cn=0;cn<lastColumn;cn++){
Cell c=r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if(c==null){
//the spreadsheet is empty in this cell
}else{
//Do something useful with the cell's contents
}
}
}
}
//获取单元格内容
/*
*To get the contents of a cell,you first need to know what kind of the cell is(asking a string cell for its numeric will
*ge you a NumberFormatException for example)
*
* 得到单元格内容，你首先需要知道它是什么类型，否则错误的类型可能会出现NumberFormatException
*/
@Test
public void getContent(){
Workbook wb=null;
try {
wb = new XSSFWorkbook(new File(str+"\\订餐统计.xlsx"));
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet1=wb.getSheetAt(0);
for(Row row:sheet1){
for(Cell cell:row){
CellReference cellRef=new CellReference(row.getRowNum(),cell.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.println(" - "); switch(cell.getCellType()){
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
System.out.println(cell.getDateCellValue());
}else{
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getBooleanCellValue());
break;
default:
System.out.println();
}
}
} } //文本提取
//对于大多数文本提取要求
@Test
public void getText(){
InputStream inp=null;
HSSFWorkbook wb=null;
try {
inp = new FileInputStream(str+"\\demo.xls");
wb=new HSSFWorkbook(new POIFSFileSystem(inp));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} ExcelExtractor extractor=new ExcelExtractor(wb); extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
String text=extractor.getText();
System.out.println(text);
} //填充和颜色
@Test
public void paddingAndColor(){
Workbook wb=new XSSFWorkbook();
Sheet sheet=wb.createSheet("new sheet"); //create a row and put some cells in it.Row are 0 based
Row row=sheet.createRow((short)1); //Aqua background aqua:浅绿色
CellStyle style=wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
Cell cell=row.createCell((short)1);
cell.setCellValue("X");
cell.setCellStyle(style); //Orange "foreground",foreground beging the fill foreground not the font color . foreground:前景
style=wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell=row.createCell((short)2);
cell.setCellValue("X");
cell.setCellStyle(style); //write to output to a file
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo8.xlsx");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //合并单元格
@Test
public void combineCell(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow((short)1);
Cell cell=row.createCell((short)1);
cell.setCellValue("this is a test of merging"); sheet.addMergedRegion(new CellRangeAddress(
1, // firstRow, 全部从0为基数
1, // lastRow,
1,// firstCol,
2// lastCol
)); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo9.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //设置字体
@Test
public void useFont(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("new sheet"); Row row=sheet.createRow(1); //create a new font and alert it
Font font=wb.createFont();
font.setFontHeight((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true); CellStyle style=wb.createCellStyle();
style.setFont(font);
Cell cell=row.createCell(1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo10.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//Note ,the maximun number of unique fonts in a workbooks is limited 32767,you should reuse fonts in you applications
//instead of creating a font for each cell
//在一个workbook中，每一个font是唯一的，好像在32767之内。你应该使用同一个Font然后循环给单元格设置样式而不是在循环中初始化多个font /*//错误的：
for(int i=0;i<10000;i++){
row=sheet.createRow(i);
cell=row.createCell((short)0); style=wb.createCellStyle();
font=wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
}
//正确的：
style=wb.createCellStyle();
font=wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
for(int i=0;i<10000;i++){
row=sheet.createRow(i);
cell=row.createCell((short)0);
cell.setCellStyle(style);
}*/
} //自定义颜色
@Test
public void customColor(){
// //HSSF中
// HSSFWorkbook wb=new HSSFWorkbook();
// HSSFSheet sheet=wb.createSheet();
// HSSFRow row=sheet.createRow((short)0);
// @SuppressWarnings("deprecation")
// HSSFCell cell=row.createCell((short)0);
// cell.setCellValue("Default Palette");
//
// //apply some colors from the standard palette,as in the previous examples
// //we will use red text on a lime background
// HSSFCellStyle style=wb.createCellStyle();
// style.setFillForegroundColor(HSSFColor.LIME.index);
// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
// HSSFFont font=wb.createFont();
// font.setColor(HSSFColor.RED.index);//字体颜色
// style.setFont(font);
//
// cell.setCellStyle(style);
//
// //save with the default palette
// try {
// FileOutputStream out=new FileOutputStream(str+"\\default_patte.xls");
// wb.write(out);
// wb.close();
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
//
// //now let's replace RED and LIMIT in the palette with a more attractive combination
// //(lovingly borrowed from freebsd.org)
// cell.setCellValue("Modified Palette");
//
// //creating a custom palette for the workbook
// HSSFPalette palette=wb.getCustomPalette();
//
// //replacing the standard red with freebsd.org red
// palette.setColorAtIndex(HSSFColor.LIME.index,
// (byte) 153,//red,
// (byte) 0,//green,
// (byte) 0//blue
// );
// //replacing lime with freebsd.org gold
// palette.setColorAtIndex(HSSFColor.LIME.index, (byte)255, (byte)204, (byte)102);
//
// //save with the modified palette
// //note that wherever we have previously used RED or LIME,the new colors magically appear
// try {
// FileOutputStream out=new FileOutputStream(str+"\\modified_palette.xls");
// wb.write(out);
// out.close();
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
//
//在XSSF中
XSSFWorkbook wb1=new XSSFWorkbook();
XSSFSheet sheet1=wb1.createSheet();
XSSFRow row1=sheet1.createRow(0);
XSSFCell cell1=row1.createCell(0);
cell1.setCellValue("custom XSSF colors"); XSSFCellStyle style1=wb1.createCellStyle();
style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128,0,128)));//设置背景颜色
style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell1.setCellStyle(style1);
try {
FileOutputStream out=new FileOutputStream(str+"\\modified_palette1.xlsx");
wb1.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//读和修改
@Test
public void readAndRewrite(){
try {
InputStream inp=new FileInputStream(str+"\\cs.xlsx");
//InputStream inp=new FileInputStream("workbook.xlsx"); Workbook wb=WorkbookFactory.create(inp);
Sheet sheet=wb.getSheetAt(0);
Row row=sheet.getRow(2);
Cell cell=row.getCell(3);
if(cell==null){
cell=row.createCell(3);
}
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("a test"); FileOutputStream fileOut=new FileOutputStream(str+"\\cs.xlsx");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //在单元格内使用文本换行
@Test
public void useNewLine(){
Workbook wb=new XSSFWorkbook();//or new HSSFWorkbook();
Sheet sheet=wb.createSheet(); Row row=sheet.createRow(2);
Cell cell=row.createCell(2);
cell.setCellValue("use \n with word warp on to create a new line"); //to enable newlines you need set a cell styles with warp==true
CellStyle cs=wb.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs); //increase row height to accomodate two lines of text
//设置行高为两个行高
row.setHeightInPoints(2*sheet.getDefaultRowHeightInPoints()); //adjust column width to fit the content
sheet.autoSizeColumn((short)2); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\ooxml-newlines.xlsx");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} } //数据格式
@Test
public void formatData(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("format sheet");
CellStyle style;
DataFormat format=wb.createDataFormat();
Row row;
Cell cell;
short rowNum=0;
short colNum=0; row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(11111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style); row=sheet.createRow(rowNum++);
cell=row.createCell(colNum);
cell.setCellValue(11111.25);
style=wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo11.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} }
//有道翻译感觉不太准确
//Fix Sheet to One Page
@Test
public void fixToOnePage(){
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("format sheet");
PrintSetup ps=sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1);
ps.setFitWidth((short)1); //create various cells and rows for spreadsheet
Row row=sheet.createRow(1);
Cell cell=row.createCell(0);
cell.setCellValue("nihao");
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo12.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//Set Print Area 设置打印区域
@Test
public void setPrintArea(){//其实我感觉不出什么东西来，不过api上有就写一下
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet("sheet1");
//sets the print area for the first sheet
wb.setPrintArea(0, "$A$1:$C$2"); //Alternatibely
wb.setPrintArea(
0,//sheetIndex,
0,//startColumn,
0,//endColumn,
0,//startRow,
0//endRow
);
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo13.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} //Set Page Numbers on Footer
@Test
public void setFooter(){
Workbook wb=new HSSFWorkbook();//or new XSSFWorkbook();
Sheet sheet=wb.createSheet("format sheet");
Footer footer=sheet.getFooter();
footer.setRight("Page "+HeaderFooter.page()+" of "+HeaderFooter.numPages()); //Create various cells and rows for spreadsheet
Row row=sheet.createRow(1);
Cell cell=row.createCell(0);
cell.setCellValue("haha");
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo14.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} } //使用一些函数
@Test
public void useSomeFunction(){
Workbook wb=new HSSFWorkbook();//new XSSFWorkbook();
Sheet sheet1=wb.createSheet("new sheet"); //create a merged region 合并区域
Row row=sheet1.createRow(1);
Row row2=sheet1.createRow(2);
Cell cell=row.createCell(1);
cell.setCellValue("This is a test of merging");
CellRangeAddress region=CellRangeAddress.valueOf("B2:E5");
sheet1.addMergedRegion(region); //Set the border and border colors
final short borderMediumDashed=CellStyle.BORDER_MEDIUM_DASHED;
RegionUtil.setBorderTop(borderMediumDashed, region, sheet1, wb);
RegionUtil.setBorderLeft(borderMediumDashed, region, sheet1, wb);
RegionUtil.setBorderBottom(borderMediumDashed, region, sheet1, wb);
RegionUtil.setRightBorderColor(borderMediumDashed, region, sheet1, wb);
RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb); //shows some usages of HSSFCellUtil
CellStyle style=wb.createCellStyle();
style.setIndention((short)4);
CellUtil.createCell(row, 8, "this is the value of the cell",style);
Cell cell2=CellUtil.createCell(row2, 8, "this is the value of the cell");
CellUtil.setAlignment(cell2,wb,CellStyle.ALIGN_CENTER); //Write out the workbook
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo15.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} }
//重复的行和列
@Test
public void repeatingRowAndCol(){
Workbook wb=new HSSFWorkbook();
Sheet sheet1=wb.createSheet("Sheet1");
Sheet sheet2=wb.createSheet("Sheet2"); //set the rows to repeat from 4 to 5 on the first sheet
sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
//set the columns to repeat from column A to C on the secend sheet
sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(str+"\\demo16.xls");
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}}

package com.test;import java.io.File;
import java.io.IOException;import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.WorkbookFactory;
/*
static int CELL_TYPE_BLANK
Blank Cell type (3)
static int CELL_TYPE_BOOLEAN
Boolean Cell type (4)
static int CELL_TYPE_ERROR
Error Cell type (5)
static int CELL_TYPE_FORMULA
Formula Cell type (2)
static int CELL_TYPE_NUMERIC
Numeric Cell type (0)
static int CELL_TYPE_STRING
String Cell type (1)
*/
public class ParseexcelDemo {
public static void main(String[] args) {
ParseexcelDemo pxd=new ParseexcelDemo();
pxd.parseXml("C:\\Users\\Administrator.PMIMP83HWNIKWOV\\Desktop\\poi\\create workbook\\学员烧烤通讯录表.xlsx");
}
public void parseXml(String filename){
Workbook wb=null;
try {
wb=WorkbookFactory.create(new File(filename));
Sheet sheet=wb.getSheetAt(0); for(Row row:sheet){ for(Cell cell:row){
System.out.print(getCellValue(cell)+"---");
}
System.out.println();
}
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} public Object getCellValue(Cell cell){
int type=cell.getCellType();
String show=null;
switch(type){
case Cell.CELL_TYPE_BLANK:
show=null;break;
case Cell.CELL_TYPE_BOOLEAN:
show= String.valueOf(cell.getBooleanCellValue());break;
case Cell.CELL_TYPE_ERROR:
show=String.valueOf(cell.getErrorCellValue());break;
case Cell.CELL_TYPE_FORMULA:
show=cell.getCellFormula();break;
case Cell.CELL_TYPE_NUMERIC:
show=String.valueOf(cell.getNumericCellValue());break;
case Cell.CELL_TYPE_STRING:
show=cell.getStringCellValue();break;
default:
show=null;
}
return show;
}
}

package com.listenerVedio;import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;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.junit.Test;public class PotTest1 {
@Test
public void TestWrite01(){
Workbook wb=new HSSFWorkbook();
FileOutputStream fos=null;
try {
fos=new FileOutputStream("d:/test/poi/w1.xls");
Sheet sheet=wb.createSheet("测试01");
Row row=sheet.createRow(0);
row.setHeightInPoints(30);//设置行高
CellStyle cs=wb.createCellStyle();//新建单元格样式对象
//我们可以使用CellStyle设置单元格的一些关于美化方向的属性，比如颜色，边框，背景等
cs.setAlignment(CellStyle.ALIGN_CENTER);//设置水平居中
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直居中
/*
cs.setBorderBottom((short)1);//设置边框样式 这里发现边框设置样式失败。原来设置边框中使用的是样式的常量
cs.setBorderTop((short)1);
cs.setBorderLeft((short)1);
cs.setBorderRight((short)1);
*/
cs.setBorderBottom(CellStyle.BORDER_DASH_DOT);
cs.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT_DOT);
Cell c=row.createCell(0);
c.setCellStyle(cs);//给单元格设置样式
c.setCellValue("标识");
c=row.createCell(1);
c.setCellStyle(cs);
c.setCellValue("用户名");
wb.write(fos);
wb.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} /**
* 发现这样写是不现实的，因为设置一个单元格样式太过麻烦。虽然使用for、循环可以达到效果
* 我 们习惯于创建一个模板，模板里面将样式定好，然后我们填充模板里面的元素，然后用来完成数据的导出
* 能够根据excel转换成对象，能够将对象导成excel，能够带模板的方式导出excel。终极目标
*
* 一些思路：如何将对象导出成excel，首先需要有对象，对象一般不是你的实体类而是专门用来导出的自定义对象，应为导出对象的列应该不是所有的
* 如何确定顶部的标题：使用注解实现。创建Annotation，在其中说明这是excel的resource，顺序也是使用注解实现
*
*/
}
}

package com.util;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;/**
* Excel模板
* @author Administrator
*
*/
public class ExcelTemplate {
private static ExcelTemplate et=new ExcelTemplate();
public final static String DATA_LINE="datas";
public final static String DEFAULT_STYLE="defaultStyle";
public final static String OWNSTYLE="style";
private Workbook wb;
private Sheet sheet;
/**
* 数据的初始化列
*/
private int initColIndex;
/**
* 数据的初始化行
*/
private int initRowIndex;
/**
* 数据的当前列
*/
private int curColIndex;
/**
* 数据的当前行
*/
private int curRowIndex;
/**
* 数据的最后一行
*/
private int lastRowIndex;
/**
* 当前行对象
*/
private Row curRow;
/**
* 默认样式
*/
private CellStyle defaultStyle;
/**
* 自定义的样式的集合
*/
private Map<Integer,CellStyle> styles;
/**
* 数据列行高
*/
private float dataLineHeight;
/**
* 单例获取et
* @return
*/
public static ExcelTemplate getInstance(){
return et;
}
/**
* 根据classPath读取模板
* @param classPath
* @return
*/
public ExcelTemplate readTemplateByClasspath(String classPath){
try {
wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath));
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("取读模板格式有误！请检查");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("取读的模板不存在！请检查");
}
initTemp();
return et;
}
/**
* 根据filePath读取模板
* @param filePath
* @return
*/
public ExcelTemplate readTemplateByFilepath(String filePath){
try {
wb=WorkbookFactory.create(new File(filePath));
}catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("取读模板格式有误！请检查");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("取读的模板不存在！请检查");
}
initTemp();
return et;
}
/**
* 将Workbook写入文件
* @param filePath
*/
public void writeToFile(String filePath){
FileOutputStream fos=null;
try {
fos=new FileOutputStream(filePath);
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("写入的文件不存在！"+e.getMessage());
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入数据失败！"+e.getMessage());
}finally{
try {
if(fos!=null){
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 将Workbook写入输出流
* @param os
*/
public void writeToStream(OutputStream os){
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入流失败！"+e.getMessage());
}
}
/**
* 初始化模板
*/
public void initTemp(){
sheet=wb.getSheetAt(0);
initConfigData();
curRow=sheet.getRow(initRowIndex);
}
/**
* 获取初始化配置数据
*/
private void initConfigData() {
lastRowIndex=sheet.getLastRowNum();
styles=new HashMap<Integer,CellStyle>();
int styleinRow=0;
for(Row row:sheet){
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(str.equals(DATA_LINE)){
curColIndex=initColIndex=cell.getColumnIndex();
curRowIndex=initRowIndex=cell.getRowIndex();
dataLineHeight=row.getHeightInPoints();
}
if(str.equals(DEFAULT_STYLE)){
defaultStyle=cell.getCellStyle();
styleinRow=cell.getRowIndex();
}
if(str.equals(OWNSTYLE)){
styles.put(cell.getColumnIndex(), cell.getCellStyle());
styleinRow=cell.getRowIndex();
} /**
* 后面可以附加好多其他东西
*/ }
}
//sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了
/*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/
sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true);
}
/**
* 创建新行
*/
public void createNewRow(){
moveData();
curRow=sheet.createRow(++curRowIndex);
curRow.setHeightInPoints(dataLineHeight);
curColIndex=initColIndex;
}
/**
* 创建新列并为新列赋值
* @param value
*/
public void createCell(String value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(int value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(double value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(boolean value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(Date value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(Calendar value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(RichTextString value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
} /**
* 将excel尾部描述列往后移动
*/
private void moveData(){
sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true);
}
/**
* 设置序号 可选
*/
public void insertSerial(){
int serialRow=-1;
int serialCol=-1;
boolean findSerial=false;
for(Row row:sheet){
if(findSerial) break;
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(str.equals("serial")){
serialRow=cell.getRowIndex();
serialCol=cell.getColumnIndex();
findSerial=true;
break;
}
}
}
if(serialRow==-1 || serialCol==-1){
return;
}
int count=curRowIndex-initRowIndex+1;
for(int i=1;i<=count;i++){
Cell c=sheet.getRow(serialRow++).createCell(serialCol);
c.setCellValue(i);
}
}
/**
* 为一些变量赋值
* @param datas
*/
public void replaceVariable(Map<String,String> datas){
for(Row row:sheet){
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(!str.startsWith("#")) continue;
if(datas.containsKey(str.substring(1))){
cell.setCellValue(datas.get(str.substring(1)));
}
}
}
}
}

package com.ann;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Column {
String title();
int order() default 9999;
}
package com.entity;import com.ann.Column;
/**
* 用户类
* @author Administrator
*
*/
public class User{
@Column(title="编号",order=1)
private int id;
@Column(title="姓名",order=2)
private String name;
@Column(title="年龄",order=3)
private int age;
@Column(title="邮箱",order=4)
private String email; public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public int getAge() {
return age;
} public void setAge(int age) {
this.age = age;
} public String getEmail() {
return email;
} public void setEmail(String email) {
this.email = email;
} public User() {
super();
} public User(int id, String name, int age, String email) {
super();
this.id = id;
this.name = name;
this.age = age;
this.email = email;
} @Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age
+ ", email=" + email + "]";
}}package com.util;
/**
* 列标题列顺序和对应对象字段组成的自定义对象
* excel和Object的中间对象
* @author Administrator
*
*/
public class ExcelHeader implements Comparable<ExcelHeader>{
/**
* excel的标题名
*/
private String title;
/**
* 每一个标题的顺序
*/
private int order;
/**
* 每一个列对应的字段名
* @return
*/
private String fieldName;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public ExcelHeader(String title, int order,String filedName) {
super();
this.title = title;
this.order = order;
this.fieldName=filedName;
}
public ExcelHeader() {
super(); } @Override
public String toString() {
return "ExcelHeader [title=" + title + ", order=" + order
+ ", fieldName=" + fieldName + "]";
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
@Override
public int compareTo(ExcelHeader o) {
return order>o.order?1:(order==o.order?0:-1);
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getFieldName() {
return fieldName;
}}
package com.util;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;/**
* Excel模板
* @author Administrator
*
*/
public class ExcelTemplate {
private static ExcelTemplate et=new ExcelTemplate();
public final static String DATA_LINE="datas";
public final static String DEFAULT_STYLE="defaultStyle";
public final static String OWNSTYLE="style";
private Workbook wb;
private Sheet sheet;
/**
* 数据的初始化列
*/
private int initColIndex;
/**
* 数据的初始化行
*/
private int initRowIndex;
/**
* 数据的当前列
*/
private int curColIndex;
/**
* 数据的当前行
*/
private int curRowIndex;
/**
* 数据的最后一行
*/
private int lastRowIndex;
/**
* 当前行对象
*/
private Row curRow;
/**
* 默认样式
*/
private CellStyle defaultStyle;
/**
* 自定义的样式的集合
*/
private Map<Integer,CellStyle> styles;
/**
* 数据列行高
*/
private float dataLineHeight;
/**
* 单例获取et
* @return
*/
public static ExcelTemplate getInstance(){
return et;
}
/**
* 根据classPath读取模板
* @param classPath
* @return
*/
public ExcelTemplate readTemplateByClasspath(String classPath){
try {
wb=WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(classPath));
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("取读模板格式有误！请检查");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("取读的模板不存在！请检查");
}
initTemp();
return et;
}
/**
* 根据filePath读取模板
* @param filePath
* @return
*/
public ExcelTemplate readTemplateByFilepath(String filePath){
try {
wb=WorkbookFactory.create(new File(filePath));
}catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("取读模板格式有误！请检查");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("取读的模板不存在！请检查");
}
initTemp();
return et;
}
/**
* 将Workbook写入文件
* @param filePath
*/
public void writeToFile(String filePath){
FileOutputStream fos=null;
try {
fos=new FileOutputStream(filePath);
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("写入的文件不存在！"+e.getMessage());
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入数据失败！"+e.getMessage());
}finally{
try {
if(fos!=null){
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 将Workbook写入输出流
* @param os
*/
public void writeToStream(OutputStream os){
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入流失败！"+e.getMessage());
}
}
/**
* 初始化模板
*/
public void initTemp(){
sheet=wb.getSheetAt(0);
initConfigData();
curRow=sheet.getRow(initRowIndex);
}
/**
* 获取初始化配置数据
*/
private void initConfigData() {
lastRowIndex=sheet.getLastRowNum();
styles=new HashMap<Integer,CellStyle>();
int styleinRow=0;
for(Row row:sheet){
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(str.equals(DATA_LINE)){
curColIndex=initColIndex=cell.getColumnIndex();
curRowIndex=initRowIndex=cell.getRowIndex();
dataLineHeight=row.getHeightInPoints();
}
if(str.equals(DEFAULT_STYLE)){
defaultStyle=cell.getCellStyle();
styleinRow=cell.getRowIndex();
}
if(str.equals(OWNSTYLE)){
styles.put(cell.getColumnIndex(), cell.getCellStyle());
styleinRow=cell.getRowIndex();
} /**
* 后面可以附加好多其他东西
*/ }
}
//sheet.removeRow(sheet.getRow(styleinRow));//这里将设置样式的一行删除，样式已经完成了
/*直接删除不能实现下面的行自动往上顶，使用移动下面的行将前面的行覆盖实现*/
sheet.shiftRows(styleinRow+1,lastRowIndex--, -1, true, true);
}
/**
* 创建新行
*/
public void createNewRow(){
moveData();
curRow=sheet.createRow(++curRowIndex);
curRow.setHeightInPoints(dataLineHeight);
curColIndex=initColIndex;
}
/**
* 创建新列并为新列赋值
* @param value
*/
public void createCell(String value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(int value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(double value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(boolean value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(Date value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(Calendar value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
}
public void createCell(RichTextString value){
Cell c=curRow.createCell(curColIndex);
if(styles.containsKey(curColIndex))
c.setCellStyle(styles.get(curColIndex));
else
c.setCellStyle(defaultStyle);
c.setCellValue(value);
curColIndex++;
} /**
* 将excel尾部描述列往后移动
*/
private void moveData(){
sheet.shiftRows(curRowIndex+1, lastRowIndex++, 1,true, true);
}
/**
* 设置序号 可选
*/
public void insertSerial(){
int serialRow=-1;
int serialCol=-1;
boolean findSerial=false;
for(Row row:sheet){
if(findSerial) break;
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(str.equals("serial")){
serialRow=cell.getRowIndex();
serialCol=cell.getColumnIndex();
findSerial=true;
break;
}
}
}
if(serialRow==-1 || serialCol==-1){
return;
}
int count=curRowIndex-initRowIndex+1;
for(int i=1;i<=count;i++){
Cell c=sheet.getRow(serialRow++).createCell(serialCol);
c.setCellValue(i);
}
}
/**
* 为一些变量赋值
* @param datas
*/
public void replaceVariable(Map<String,String> datas){
for(Row row:sheet){
for(Cell cell:row){
if(cell.getCellType()!=Cell.CELL_TYPE_STRING) continue;
String str=cell.getStringCellValue().trim();
if(!str.startsWith("#")) continue;
if(datas.containsKey(str.substring(1))){
cell.setCellValue(datas.get(str.substring(1)));
}
}
}
}
}
package com.util;import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.ann.Column;/**
* 对象和excel之间的互相转换
* 使用模板写入excel与不使用模板写入
* 读取excel数据到对象集合
* @author Administrator
*
*/
@SuppressWarnings({"rawtypes","unchecked"})
public class ExcelUtil {
private static ExcelUtil eu=new ExcelUtil();
/**
* 单例
*/
private ExcelUtil(){}
/**
* excel字段内容对象集合
*/
private List<ExcelHeader> headers; public static ExcelUtil getInstance(){
return eu;
}
/**
* 模板对象
*/
private ExcelTemplate et;
/**
* excel工作薄对象
*/
private Workbook wb;
/**
* 将对象插入到excel表格中，使用模板，目标是文件
* @param datas
* @param alertableValues
* @param objectClassType
* @param path
* @param isFilePath
* @param outPath
*/
public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,String outPath){
et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
try {
handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType); et.writeToFile(outPath);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}
/**
* 将对象插入到excel表格中，使用模板，目标是输出流
* @param datas
* @param alertableValues
* @param objectClassType
* @param path
* @param isFilePath
* @param os
*/
public void Obj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType,String path,boolean isFilePath,OutputStream os){
et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
try {
handerObj2ExcelUseTemplate(datas,alertableValues,objectClassType);
et.writeToStream(os);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}
/**
* 将对象插入到excel表格中，使用模板，目标是文件
* @param datas
* @param alertableValues
* @param objectClassType
* @param path
* @param isFilePath
* @param outPath
*/ public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,String outPath){
et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
try {
Map maps=new HashMap<String,String>();
if(!(prop==null || prop.size()==0)) maps.putAll(prop);
handerObj2ExcelUseTemplate(datas,maps,objectClassType);
et.writeToFile(outPath);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}
/**
* 将对象插入到excel表格中，使用模板，目标是输出流
* @param datas
* @param alertableValues
* @param objectClassType
* @param path
* @param isFilePath
* @param os
*/
public void Obj2ExcelUseTemplate(List<Object> datas,Properties prop,Class objectClassType,String path,boolean isFilePath,OutputStream os){
et=isFilePath?ExcelTemplate.getInstance().readTemplateByFilepath(path):ExcelTemplate.getInstance().readTemplateByClasspath(path);
try {
Map maps=new HashMap<String,String>();
if(!(prop==null || prop.size()==0)) maps.putAll(prop);
handerObj2ExcelUseTemplate(datas,maps,objectClassType);
et.writeToStream(os);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}
/**
* 使用模板将对象插入Excel中的转换工作的方法
* @param datas
* @param alertableValues
* @param objectClassType
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
* @throws InstantiationException
*/
private void handerObj2ExcelUseTemplate(List<Object> datas,Map<String,String> alertableValues,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException{
headers=getHeaderList(objectClassType);
Collections.sort(headers);
for(ExcelHeader eh:headers){//设置标题
et.createCell(eh.getTitle());
}
for(Object o:datas){
et.createNewRow();
for(ExcelHeader eh:headers){
et.createCell(BeanUtils.getProperty(o, eh.getFieldName()));
}
}
if(alertableValues==null || alertableValues.size()==0) return;
et.replaceVariable(alertableValues);
}
/**
* 获取excel字段内容对象集合
* @param clz
* @return
*/
private List<ExcelHeader> getHeaderList(Class clz) {
List<ExcelHeader> headers=new ArrayList<ExcelHeader>();
Field[] fields=clz.getDeclaredFields();
for(Field f:fields){
if(f.isAnnotationPresent(Column.class)){
Column c=f.getAnnotation(Column.class);
headers.add(new ExcelHeader(c.title(), c.order(), f.getName()));
}
}
return headers;
}
/**
* 对象写入excel文件
* @param isXSSF
* @param datas
* @param objectClassType
* @param outPath
*/
public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,String outPath){
wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook());
try {
Obj2Excel(datas,objectClassType);
writeToFile(outPath);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
/**
* 对象写入excel文件 流
* @param isXSSF
* @param datas
* @param objectClassType
* @param os
*/
public void Obj2Excel(boolean isXSSF,List<Object> datas,Class objectClassType,OutputStream os){
wb=isXSSF?(new XSSFWorkbook()):(new HSSFWorkbook());
try {
Obj2Excel(datas,objectClassType);
writeToStream(os);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
/**
* 将Workbook写入文件
* @param outPath
*/
private void writeToFile(String outPath) {
FileOutputStream fos=null;
try {
fos = new FileOutputStream(outPath);
wb.write(fos);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
if(fos!=null) fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 将Workbook写入输出流
* @param os
*/
private void writeToStream(OutputStream os){
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 处理将对象转换成excel的具体实现，不使用模板
* @param datas
* @param objectClassType
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
private void Obj2Excel(List<Object> datas,Class objectClassType) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
Sheet sheet=wb.createSheet();
Row r=sheet.createRow(0);
//插入表头
headers=getHeaderList(objectClassType);
Collections.sort(headers);
for(int i=0;i<headers.size();i++){
Cell c=r.createCell(i);
c.setCellValue(headers.get(i).getTitle());
}
//插入数据内容
for(int i=0;i<datas.size();i++){
r=sheet.createRow(i+1);
for(int j=0;j<headers.size();j++){
Cell c=r.createCell(j);
c.setCellValue(BeanUtils.getProperty(datas.get(i),headers.get(j).getFieldName()));
}
}
}
/**
* 从流中读取表格信息到对象集合
* @param ins
* @param objectClassType
* @param readLine
* @param tailLine
* @return
*/
public List<Object> readExcel2Objs(InputStream ins,Class objectClassType,int readLine,int tailLine){
try {
wb=WorkbookFactory.create(ins);
return handerReadExcel2Objs(objectClassType,readLine,tailLine);
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return null;
}
/**
* 从文件中读取表格信息到对象集合
* @param filePath
* @param objectClassType
* @param readLine
* @param tailLine
* @return
*/
public List<Object> readExcel2Objs(String filePath,Class objectClassType,int readLine,int tailLine){
try {
wb=WorkbookFactory.create(new File(filePath));
return handerReadExcel2Objs(objectClassType,readLine,tailLine);
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return null;
}
/**
* 转换表格信息到对象集合的实现方法
* @param objectClassType
* @param readLine
* @param tailLine
* @return
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
*/
private List<Object> handerReadExcel2Objs(Class objectClassType,int readLine,int tailLine) throws InstantiationException, IllegalAccessException, InvocationTargetException{
List<Object> objs=new ArrayList<Object>();
Sheet sheet=wb.getSheetAt(0);
Map<Integer,String> headerMap=getHeaderMap(sheet.getRow(readLine),objectClassType);//列数和字段的map集合
for(int i=readLine+1;i<sheet.getLastRowNum()-tailLine+1;i++){
Row r=sheet.getRow(i);
Object o=objectClassType.newInstance();
Set<Integer> cols=headerMap.keySet();
for(int x:cols){
Cell c=r.getCell(x);
BeanUtils.copyProperty(o, headerMap.get(x), getCellValue(c));
}
objs.add(o);
}
return objs;
}
/**
* 获取不同值的方法
* @param cell
* @return
*/
private Object getCellValue(Cell cell){
int type=cell.getCellType();
String show=null;
switch(type){
case Cell.CELL_TYPE_BLANK:
show=null;break;
case Cell.CELL_TYPE_BOOLEAN:
show= String.valueOf(cell.getBooleanCellValue());break;
case Cell.CELL_TYPE_ERROR:
show=String.valueOf(cell.getErrorCellValue());break;
case Cell.CELL_TYPE_FORMULA:
show=cell.getCellFormula();break;
case Cell.CELL_TYPE_NUMERIC:
show=String.valueOf(cell.getNumericCellValue());break;
case Cell.CELL_TYPE_STRING:
show=cell.getStringCellValue();break;
default:
show=null;
}
return show;
}
/**
* 获取列和对应字段的映射集合
*/
public Map<Integer,String> getHeaderMap(Row r,Class objectClassType){
headers=getHeaderList(objectClassType);
Collections.sort(headers);
Map<Integer,String> maps=new HashMap<Integer,String>();
for(Cell c:r){
String title=c.getStringCellValue();
for(ExcelHeader eh:headers){
if(title.equals(eh.getTitle())){
maps.put(c.getColumnIndex(), eh.getFieldName());
break;
}
}
}
return maps;
}
}一个用来测试的properties：
title="User Message List"
date="2016-7-11"
author="guodaxia"