Java读写Excel之POI超入门

原创

mb63e0703549da8 2023-02-15 17:03:43 博主文章分类：java ©著作权

文章标签 java System Java sed 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者mb63e0703549da8的原创作品，请联系作者获取转载授权，否则将追究法律责任

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。

Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

[list]
[*]HSSF －提供读写Microsoft Excel XLS格式档案的功能。
[*]XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。
[*]HWPF －提供读写Microsoft Word DOC格式档案的功能。
[*]HSLF －提供读写Microsoft PowerPoint格式档案的功能。
[*]HDGF －提供读Microsoft Visio格式档案的功能。
[*]HPBF －提供读Microsoft Publisher格式档案的功能。
[*]HSMF －提供读Microsoft Outlook格式档案的功能。
[/list]

[url]http://poi.apache.org/[/url]

版本：poi-3.8-20120326.jar

1、生成Workbook

//生成Workbook  
HSSFWorkbook wb = new HSSFWorkbook();  

//添加Worksheet（不添加sheet时生成的xls文件打开时会报错）  
@SuppressWarnings("unused")  
Sheet sheet1 = wb.createSheet();  
@SuppressWarnings("unused")  
Sheet sheet2 = wb.createSheet();  
@SuppressWarnings("unused")  
Sheet sheet3 = wb.createSheet("new sheet");  
@SuppressWarnings("unused")  
Sheet sheet4 = wb.createSheet("rensanning");  

//保存为Excel文件  
FileOutputStream out = null;  

try {  
    out = new FileOutputStream("c:\\text.xls");  
    wb.write(out);        
} catch (IOException e) {  
    System.out.println(e.toString());  
} finally {  
    try {  
        out.close();  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    }  
}

2、生成Workbook OOXML形式(.xlsx)

//生成Workbook  
XSSFWorkbook wb = new XSSFWorkbook();  

//......

3、打开Workbook

//方法一：使用WorkbookFactory  
FileInputStream in = null;  
Workbook wb = null;  

try {  
    in = new FileInputStream(TEST_WORKBOOK_NAME);  
    wb = WorkbookFactory.create(in);  
} catch (IOException e) {  
    System.out.println(e.toString());  
} catch (InvalidFormatException e) {  
    System.out.println(e.toString());  
} finally {  
    try {  
        in.close();  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    }  
}  

System.out.println("====================Workbook====================");  
System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
System.out.println("Sheet3's name:" + wb.getSheetName(3));  
System.out.println();  

//方法二：使用POIFSFileSystem  
try {  
    in = new FileInputStream(TEST_WORKBOOK_NAME);  
    POIFSFileSystem fs = new POIFSFileSystem(in);  
    wb = new HSSFWorkbook(fs);  
} catch (IOException e) {  
    System.out.println(e.toString());  
} finally {  
    try {  
        in.close();  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    }  
}  

System.out.println("====================Workbook====================");  
System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
System.out.println("Sheet3's name:" + wb.getSheetName(3));  
System.out.println();

4、打开加密的Workbook(读加密)

FileInputStream input = new FileInputStream(TEST_WORKBOOK_NAME_ENCRYPTED);  
BufferedInputStream binput = new BufferedInputStream(input);  
POIFSFileSystem poifs = new POIFSFileSystem(binput);  

Biff8EncryptionKey.setCurrentUserPassword(TEST_WORKBOOK_PASSWORD);  

HSSFWorkbook wb = new HSSFWorkbook(poifs);  

System.out.println("====================EncryptedWorkbook====================");  
System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
System.out.println("Sheet0's name:" + wb.getSheetName(0));  
System.out.println();

5、追加Sheet

Sheet sheet = wb.createSheet("append sheet");

6、复制Sheet

wb.cloneSheet(1);

7、修改Sheet名称

wb.setSheetName(i, "SheetName new");

8、删除Sheet

wb.removeSheetAt(1);

9、设置下部Sheet名的Tab的第一个可见Tab

//设置下部Sheet名的Tab的第一个可见Tab（以左的Sheet看不见）  
wb.setFirstVisibleTab(2);

10、调整Sheet顺序

wb.setSheetOrder("SheetName3", 1);  
wb.setSheetOrder(wb.getSheetName(4), 0);

11、设置当前Sheet

t.setActiveSheet();

//设置当前Sheet  
wb.setActiveSheet(wb.getNumberOfSheets() - 1);  
//（Excel的当前Sheet被设置，需要结合setSelected使用，不然下部Sheet名的Tab还是默认为第一个）  
//（需要选择多个Sheet的话，每个Sheet调用setSelected(true)即可）  
wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);

12、固定窗口

wb.getSheet("SheetName4").createFreezePane(2, 2);

[img]http://dl.iteye.com/upload/attachment/0069/0777/83d3f34d-5d7c-3f81-a35c-fe0ba21ec136.gif[/img]

13、分割窗口

wb.getSheet("SheetName5").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

[img]http://dl.iteye.com/upload/attachment/0069/0779/1ffee4db-8958-3c31-977f-bab0a50e90ff.gif[/img]

14、Sheet缩放

//setZoom(int numerator, int denominator)  
//"numerator"÷"denominator"  例如： 3÷1＝3 那就是设置为300%  

//扩大(200%)  
wb.getSheet("sheetname1").setZoom(2, 1);  
//缩小(50%)  
wb.getSheet("sheetname2").setZoom(1, 2);

[img]http://dl.iteye.com/upload/attachment/0069/0781/b89a9c89-901a-3115-a121-48c373b90571.gif[/img]

15、行列分组

wb.getSheet("sheetname3").groupColumn(4, 7);  
wb.getSheet("sheetname3").groupColumn(9, 12);  
wb.getSheet("sheetname3").groupColumn(10, 11);  

wb.getSheet("sheetname3").groupRow(5, 14);  
wb.getSheet("sheetname3").groupRow(7, 13);  
wb.getSheet("sheetname3").groupRow(16, 19);

[img]http://dl.iteye.com/upload/attachment/0069/0783/c96dce33-32c6-3f42-b00e-85f0ea7c9164.gif[/img]

16、关闭分组

wb.getSheet("sheetname3").setColumnGroupCollapsed(10, true);  
wb.getSheet("sheetname3").setRowGroupCollapsed(7, true);

17、插入行

Row row1 = wb.getSheet("sheetname4").createRow(1);  
Cell cell1_1 = row1.createCell(1);  
cell1_1.setCellValue(123);  

Row row4 = wb.getSheet("sheetname4").createRow(4);  
Cell cell4_3 = row4.createCell(3);  
cell4_3.setCellValue("中国");

18、删除行

Row row = wb.getSheet("sheetname4").getRow(1);  
wb.getSheet("sheetname4").removeRow(row);

19、移动行

//******移动行只移动内容，不牵扯行的删除和插入  

//移动行（把第1行和第2行移到第5行之后）  
wb.getSheet("sheetname5").shiftRows(0, 1, 5);  

//移动行（把第3行和第4行往上移动1行）  
wb.getSheet("sheetname5").shiftRows(2, 3, -1);

20、修改行高

//设置默认行高  
wb.getSheet("sheetname6").setDefaultRowHeight((short)100);  

//设置行高  
wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));

21、修改列宽

//设置默认列宽  
wb.getSheet("sheetname7").setDefaultColumnWidth(12);  

//设置列宽  
wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);

22、不显示网格线

//不显示网格线  
wb.getSheet("sheetname8").setDisplayGridlines(false);

[img]http://dl.iteye.com/upload/attachment/0069/0785/d3a42566-df9d-314f-9e2f-0a563e8e7e93.gif[/img]

23、设置分页

//设置第一页：3行2列 （可以多次设置）  
wb.getSheet("sheetname9").setRowBreak(2);  
wb.getSheet("sheetname9").setColumnBreak(1);

[img]http://dl.iteye.com/upload/attachment/0069/0787/9dbe89e3-b5c1-3dd4-a5f6-f71b3f5436ff.gif[/img]

24、添加，删除，合并单元格

//追加行  
for (int i = 0; i < 10; i++) {  
    Row row = wb.getSheet("sheetname10").createRow(i);  
    for (int j = 0; j < 10; j++) {  
        //添加单元格  
        Cell cell = row.createCell(j);  
        cell.setCellValue(i + 1);  
    }  

    //删除单元格  
    row.removeCell(row.getCell(5));  
}  

//合并单元格  
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)  
wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));

[img]http://dl.iteye.com/upload/attachment/0069/0789/8391acae-73e9-3dc8-a1c0-cbd9fbda40fa.gif[/img]

25、设置Header，Footer

//Header  
Header header = wb.getSheet("sheetname11").getHeader();  
header.setLeft(HSSFHeader.startUnderline() +  
        HSSFHeader.font("宋体", "Italic") +  
        "文字文字" +  
        HSSFHeader.endUnderline());  
header.setCenter(HSSFHeader.fontSize((short)16) +  
        HSSFHeader.startDoubleUnderline() +  
        HSSFHeader.startBold() +  
        "汉字汉字" +  
        HSSFHeader.endBold() +  
        HSSFHeader.endDoubleUnderline());  
header.setRight("打印时间:" + HSSFHeader.date() +  " " + HSSFHeader.time());  

//Footer  
Footer footer = wb.getSheet("sheetname11").getFooter();  
footer.setLeft("Copyright @ rensanning");  
footer.setCenter("Page:" + HSSFFooter.page() + " / " + HSSFFooter.numPages());  
footer.setRight("File:" + HSSFFooter.file());

[img]http://dl.iteye.com/upload/attachment/0069/0791/27253ea5-779b-3a58-b849-a1405fe5146b.gif[/img]

[img]http://dl.iteye.com/upload/attachment/0069/0793/7191d1f6-8e3a-3af0-9c5a-878d5f66b6c2.gif[/img]

26、设置单元格值

//boolean  
Cell cell00 = rows[0].createCell(0);  
boolean val00 = true;  
cell00.setCellValue(val00);  

//Calendar 格式化  
CellStyle styleCalendar = wb.createCellStyle();  
DataFormat formatCalendar = wb.createDataFormat();  
styleCalendar.setDataFormat(formatCalendar.getFormat("yyyy/mm/dd"));  
Cell cell11 = rows[1].createCell(0);  
Calendar val11 = Calendar.getInstance();  
cell11.setCellStyle(styleCalendar);  
cell11.setCellValue(val11);  

//Date 格式化  
CellStyle styleDate = wb.createCellStyle();  
DataFormat formatDate = wb.createDataFormat();  
styleDate.setDataFormat(formatDate.getFormat("yyyy/mm/dd hh:mm"));  
Cell cell21 = rows[2].createCell(0);  
Date val21 = new Date();  
cell21.setCellStyle(styleDate);  
cell21.setCellValue(val21);  

//double  
Cell cell30 = rows[3].createCell(0);  
double val30 = 1234.56;  
cell30.setCellValue(val30);  

//double 格式化  
CellStyle styleDouble = wb.createCellStyle();  
DataFormat formatDouble = wb.createDataFormat();  
styleDouble.setDataFormat(formatDouble.getFormat("#,##0.00"));  
Cell cell31 = rows[3].createCell(1);  
double val31 = 1234.56;  
cell31.setCellStyle(styleDouble);  
cell31.setCellValue(val31);  

//String  
Cell cell40 = rows[4].createCell(0);  
HSSFRichTextString val40 = new HSSFRichTextString("Test汉字");  
cell40.setCellValue(val40);

[img]http://dl.iteye.com/upload/attachment/0069/0795/24577304-b6c7-37d5-be16-6db608b2988b.gif[/img]

27、设置单元格边线

wb.getSheet("sheetname2").setColumnWidth(1, 4096);  

Row row1 = wb.getSheet("sheetname2").createRow(1);  
row1.setHeightInPoints(70);  

Cell cell1_1 = row1.createCell(1);  
cell1_1.setCellValue("Sample");  

CellStyle style = wb.createCellStyle();  

style.setBorderTop(CellStyle.BORDER_DASHED);  
style.setBorderBottom(CellStyle.BORDER_DOUBLE);  
style.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT);  
style.setBorderRight(CellStyle.BORDER_MEDIUM);  

style.setTopBorderColor(IndexedColors.MAROON.getIndex());  
style.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());  
style.setLeftBorderColor(IndexedColors.ORANGE.getIndex());  
style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());  

cell1_1.setCellStyle(style);

[img]http://dl.iteye.com/upload/attachment/0069/0797/fb18218d-aba3-3a65-bcfa-30e7557c5bdf.gif[/img]

28、设置单元格背景填充

wb.getSheet("sheetname3").setColumnWidth(0, 4096);  
wb.getSheet("sheetname3").setColumnWidth(1, 4096);  
wb.getSheet("sheetname3").setColumnWidth(2, 4096);  

Row row1 = wb.getSheet("sheetname3").createRow(1);  
row1.setHeightInPoints(70);  

Cell cell1_0 = row1.createCell(0);  
Cell cell1_1 = row1.createCell(1);  
Cell cell1_2 = row1.createCell(2);  

cell1_0.setCellValue("THIN_VERT_BANDS");  
cell1_1.setCellValue("BIG_SPOTS");  
cell1_2.setCellValue("THICK_HORZ_BANDS");  

CellStyle style1 = wb.createCellStyle();  
style1.setFillPattern(CellStyle.THIN_VERT_BANDS);  
style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());  
style1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());  

CellStyle style2 = wb.createCellStyle();  
style2.setFillPattern(CellStyle.BIG_SPOTS);  
style2.setFillForegroundColor(IndexedColors.RED.getIndex());  
style2.setFillBackgroundColor(IndexedColors.WHITE.getIndex());  

CellStyle style3 = wb.createCellStyle();  
style3.setFillPattern(CellStyle.THICK_HORZ_BANDS);  
style3.setFillForegroundColor(IndexedColors.PINK.getIndex());  
style3.setFillBackgroundColor(IndexedColors.BROWN.getIndex());  

cell1_0.setCellStyle(style1);  
cell1_1.setCellStyle(style2);  
cell1_2.setCellStyle(style3);

[img]http://dl.iteye.com/upload/attachment/0069/0799/b8323be3-bec2-3aa6-a333-eac80b7c0727.gif[/img]

29、设置单元格注释

HSSFCreationHelper createHelper =  
    (HSSFCreationHelper)wb.getCreationHelper();  
Drawing patriarch = wb.getSheet("sheetname4").createDrawingPatriarch();  

//注释  
Row row = wb.getSheet("sheetname4").createRow(1);  
Cell cell = row.createCell(1);  

HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0,  
        (short) 4, 2, (short) 6, 5);  

Comment comment = patriarch.createCellComment(clientAnchor);  
comment.setString(createHelper.createRichTextString("注释注释111"));  
comment.setAuthor("rensanning");  

cell.setCellComment(comment);  

//带字体的注释  
Row row2 = wb.getSheet("sheetname4").createRow(2);  
Cell cell2 = row2.createCell(1);  

Font font = wb.createFont();  
font.setFontName("宋体");  
font.setFontHeightInPoints((short)10);  
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
font.setColor(HSSFColor.RED.index);  

Comment comment2 = patriarch.createCellComment(clientAnchor);  
HSSFRichTextString text = new HSSFRichTextString("注释注释222");  
text.applyFont(font);  
comment2.setString(text);  
comment2.setAuthor("rensanning");  

cell2.setCellComment(comment2);

30、设置单元格字体（斜体，粗体，下线，取消线，字体，大小，背景色）

Font font = null;  
CellStyle style = null;  

//斜体  
font = wb.createFont();  
font.setItalic(true);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(1).getCell(1).setCellStyle(style);  

//粗体  
font = wb.createFont();  
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(2).getCell(1).setCellStyle(style);  

//字体名  
font = wb.createFont();  
font.setFontName("Courier New");  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(3).getCell(1).setCellStyle(style);  

//字体大小  
font = wb.createFont();  
font.setFontHeightInPoints((short)20);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(4).getCell(1).setCellStyle(style);  

//文字颜色  
font = wb.createFont();  
font.setColor(HSSFColor.YELLOW.index);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(5).getCell(1).setCellStyle(style);  

//上标  
font = wb.createFont();  
font.setTypeOffset(HSSFFont.SS_SUPER);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(6).getCell(1).setCellStyle(style);  

//下标  
font = wb.createFont();  
font.setTypeOffset(HSSFFont.SS_SUB);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(7).getCell(1).setCellStyle(style);  

//删除线  
font = wb.createFont();  
font.setStrikeout(true);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(8).getCell(1).setCellStyle(style);  

//下划线  
font = wb.createFont();  
font.setUnderline(HSSFFont.U_SINGLE);  
style = wb.createCellStyle();  
style.setFont(font);  

wb.getSheet("sheetname5").getRow(9).getCell(1).setCellStyle(style);  

//背景色  
style = wb.createCellStyle();  
style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);  
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  

style.setFont(font);  

wb.getSheet("sheetname5").getRow(10).getCell(1).setCellStyle(style);

[img]http://dl.iteye.com/upload/attachment/0069/0801/aced02c4-2fef-3c34-9826-db9e904c038e.gif[/img]

31、设置超链接

HSSFCreationHelper createHelper =  
    (HSSFCreationHelper)wb.getCreationHelper();  

CellStyle style = wb.createCellStyle();  
Font font = wb.createFont();  
font.setUnderline(HSSFFont.U_SINGLE);  
font.setColor(HSSFColor.BLUE.index);  
style.setFont(font);  

//追加行  
Row[] rows = new Row[10];  
for (int i = 0; i < 10; i++) {  
    rows[i] = wb.getSheet("sheetname6").createRow(i);  
}  

//URL  
rows[0].createCell(0).setCellValue("URL Link");  

HSSFHyperlink link1 = createHelper.createHyperlink(HSSFHyperlink.LINK_URL);  
link1.setAddress("http://poi.apache.org/");  
rows[0].getCell(0).setHyperlink(link1);  
rows[0].getCell(0).setCellStyle(style);  

//Mail  
rows[1].createCell(0).setCellValue("Email Link");  

HSSFHyperlink link2 = createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);  
link2.setAddress("mailto:poi@apache.org?subject=Hyperlinks");  
rows[1].getCell(0).setHyperlink(link2);  
rows[1].getCell(0).setCellStyle(style);  

//File  
rows[2].createCell(0).setCellValue("File Link");  

HSSFHyperlink link3 = createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);  
link3.setAddress("link.xls");  
rows[2].getCell(0).setHyperlink(link3);  
rows[2].getCell(0).setCellStyle(style);  

//Workbook内  
rows[3].createCell(0).setCellValue("Worksheet Link");  

HSSFHyperlink link4 = createHelper.createHyperlink(HSSFHyperlink.LINK_DOCUMENT);  
link4.setAddress("sheetname1!A1");  
rows[3].getCell(0).setHyperlink(link4);  
rows[3].getCell(0).setCellStyle(style);

32、设置单元格横向对齐，纵向对齐

//横向对齐  
wb.getSheet("sheetname7").setColumnWidth(2, 3072);  

Row[] row = new Row[7];  
Cell[] cell = new Cell[7];  

for (int i = 0 ; i < 7 ; i++){  
  row[i] = wb.getSheet("sheetname7").createRow(i + 1);  
  cell[i] = row[i].createCell(2);  
  cell[i].setCellValue("Please give me a receipt");  
}  

CellStyle style0 = wb.createCellStyle();  
style0.setAlignment(CellStyle.ALIGN_GENERAL);  
cell[0].setCellStyle(style0);  

CellStyle style1 = wb.createCellStyle();  
style1.setAlignment(CellStyle.ALIGN_LEFT);  
cell[1].setCellStyle(style1);  

CellStyle style2 = wb.createCellStyle();  
style2.setAlignment(CellStyle.ALIGN_CENTER);  
cell[2].setCellStyle(style2);  

CellStyle style3 = wb.createCellStyle();  
style3.setAlignment(CellStyle.ALIGN_RIGHT);  
cell[3].setCellStyle(style3);  

CellStyle style4 = wb.createCellStyle();  
style4.setAlignment(CellStyle.ALIGN_FILL);  
cell[4].setCellStyle(style4);  

CellStyle style5 = wb.createCellStyle();  
style5.setAlignment(CellStyle.ALIGN_JUSTIFY);  
cell[5].setCellStyle(style5);  

CellStyle style6 = wb.createCellStyle();  
style6.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);  
cell[6].setCellStyle(style6);  

//纵向对齐  
Row row2 = wb.getSheet("sheetname8").createRow(1);  
row2.setHeightInPoints(70);  
Cell[] cell2 = new Cell[4];  

for (int i = 0 ; i < 4 ; i++){  
    cell2[i] = row2.createCell(i + 1);  
    cell2[i].setCellValue("Please give me a receipt");  
}  

CellStyle style02 = wb.createCellStyle();  
style02.setVerticalAlignment(CellStyle.VERTICAL_TOP);  
cell2[0].setCellStyle(style02);  

CellStyle style12 = wb.createCellStyle();  
style12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
cell2[1].setCellStyle(style12);  

CellStyle style22 = wb.createCellStyle();  
style22.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);  
cell2[2].setCellStyle(style22);  

CellStyle style32 = wb.createCellStyle();  
style32.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);  
cell2[3].setCellStyle(style32);

[img]http://dl.iteye.com/upload/attachment/0069/0804/1f5cb399-20bd-359e-8d16-bfc9edbac1ce.gif[/img]

33、设置单元格旋转角度

Row[] row = new Row[4];  
Cell[] cell = new Cell[4];  

for (int i = 0 ; i < 4 ; i++){  
  row[i] = wb.getSheet("sheetname9").createRow(i + 1);  
  cell[i] = row[i].createCell(2);  
  cell[i].setCellValue("Coffee");  
}  

CellStyle style0 = wb.createCellStyle();  
style0.setRotation((short)45);  
cell[0].setCellStyle(style0);  

CellStyle style1 = wb.createCellStyle();  
style1.setRotation((short)0);  
cell[1].setCellStyle(style1);  

CellStyle style2 = wb.createCellStyle();  
style2.setRotation((short)-45);  
cell[2].setCellStyle(style2);  

CellStyle style3 = wb.createCellStyle();  
style3.setRotation((short)-90);  
cell[3].setCellStyle(style3);

[img]http://dl.iteye.com/upload/attachment/0069/0806/e699ee93-a721-3010-800b-d1df29e0130d.gif[/img]

34、设置单元格自动折行

Row[] row = new Row[2];  
Cell[] cell = new Cell[2];  

for (int i = 0 ; i < 2 ; i++){  
  row[i] = wb.getSheet("sheetname10").createRow(i + 1);  
  cell[i] = row[i].createCell(2);  
  cell[i].setCellValue("Thank you very much.");  
}  

CellStyle style0 = wb.createCellStyle();  
style0.setWrapText(true);  
cell[0].setCellStyle(style0);  

CellStyle style1 = wb.createCellStyle();  
style1.setWrapText(false);  
cell[1].setCellStyle(style1);

[img]http://dl.iteye.com/upload/attachment/0069/0808/9a7b6c47-0c12-39df-910c-da3b089f0dbb.gif[/img]

35、设置单元格文字缩进

Row[] row = new Row[4];  
Cell[] cell = new Cell[4];  

for (int i = 0 ; i < 4 ; i++){  
  row[i] = wb.getSheet("sheetname11").createRow(i + 1);  
  cell[i] = row[i].createCell(2);  
  cell[i].setCellValue("Coffee");  
}  

CellStyle style1 = wb.createCellStyle();  
style1.setIndention((short)1);  
style1.setAlignment(CellStyle.ALIGN_LEFT);  
cell[1].setCellStyle(style1);  

CellStyle style2 = wb.createCellStyle();  
style2.setIndention((short)2);  
style2.setAlignment(CellStyle.ALIGN_LEFT);  
cell[2].setCellStyle(style2);  

CellStyle style3 = wb.createCellStyle();  
style3.setIndention((short)3);  
style3.setAlignment(CellStyle.ALIGN_LEFT);  
cell[3].setCellStyle(style3);

[img]http://dl.iteye.com/upload/attachment/0069/0810/73257bb4-177d-3eae-987b-b53477a7267a.gif[/img]

36、自定义格式

Row[] rows = new Row[2];  
for (int i = 0; i < rows.length; i++) {  
    rows[i] = wb.getSheet("sheetname12").createRow(i + 1);  
}  
DataFormat format = wb.createDataFormat();  

CellStyle[] styles = new CellStyle[2];  
for (int i = 0; i < styles.length; i++) {  
    styles[i] = wb.createCellStyle();  
}  
styles[0].setDataFormat(format.getFormat("0.0"));  
styles[1].setDataFormat(format.getFormat("#,##0.000"));  

Cell[] cells = new Cell[2];  
for (int i = 0; i < cells.length; i++)  {  
    cells[i] = rows[i].createCell(1);  
    cells[i].setCellValue(1111.25);  

    cells[i].setCellStyle(styles[i]);  
}

[img]http://dl.iteye.com/upload/attachment/0069/0812/c7a19ae0-e208-3536-9556-27c553115501.gif[/img]

37、设置公式

Row row1 = wb.getSheet("sheetname13").createRow(1);  
Row row2 = wb.getSheet("sheetname13").createRow(2);  

Cell cell1_1 = row1.createCell(1);  
Cell cell1_2 = row1.createCell(2);  
Cell cell1_3 = row1.createCell(3);  
Cell cell2_3 = row2.createCell(3);  

cell1_1.setCellValue(30);  
cell1_2.setCellValue(25);  
cell1_3.setCellFormula("B2+C2");  
cell2_3.setCellFormula("MOD(B2,C2)");

[img]http://dl.iteye.com/upload/attachment/0069/0814/c1ca84a0-e76b-32b1-8d51-d860d4bf5f81.gif[/img]

38、画直线，圆圈（椭圆），正方形（长方形）,Textbox

HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname14")).createDrawingPatriarch();  

//直线  
HSSFClientAnchor clientAnchor1 = new HSSFClientAnchor(0, 0, 0, 0,  
        (short) 4, 2, (short) 6, 5);  
HSSFSimpleShape shape1 = patriarch.createSimpleShape(clientAnchor1);  
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);  

//圆圈（椭圆）  
HSSFClientAnchor clientAnchor2 = new HSSFClientAnchor(0, 0, 0, 0,  
        (short) 8, 4, (short) 6, 5);  
HSSFSimpleShape shape2 = patriarch.createSimpleShape(clientAnchor2);  
shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);  

//正方形（长方形）  
HSSFClientAnchor clientAnchor3 = new HSSFClientAnchor(0, 0, 0, 0,  
        (short) 12, 6, (short) 6, 5);  
HSSFSimpleShape shape3 = patriarch.createSimpleShape(clientAnchor3);  
shape3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);  

//Textbox  
HSSFClientAnchor clientAnchor4 = new HSSFClientAnchor(0, 0, 0, 0,  
        (short) 14, 8, (short) 6, 5);  
HSSFTextbox textbox = patriarch.createTextbox(clientAnchor4);  
textbox.setString(new HSSFRichTextString("This is a test"));

39、插入图片

//需要commons-codec-1.6.jar  
FileInputStream jpeg = new FileInputStream("resource/test.jpg");  
byte[] bytes = IOUtils.toByteArray(jpeg);  
int pictureIndex = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);  
jpeg.close();  

HSSFCreationHelper helper = (HSSFCreationHelper) wb.getCreationHelper();  

HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname15")).createDrawingPatriarch();  

HSSFClientAnchor clientAnchor = helper.createClientAnchor();  

clientAnchor.setCol1(3);  
clientAnchor.setRow1(2);  

HSSFPicture picture = patriarch.createPicture(clientAnchor, pictureIndex);  
picture.resize();

[img]http://dl.iteye.com/upload/attachment/0069/0816/4c94a43b-1703-3923-badf-da36b6c00aff.gif[/img]

40、设置可输入List

CellRangeAddressList addressList = new CellRangeAddressList(  
        0,  
        0,  
        0,  
        0);  

final String[] DATA_LIST = new String[] {  
        "10",  
        "20",  
        "30",  
};  
DVConstraint dvConstraint =  
    DVConstraint.createExplicitListConstraint(DATA_LIST);  

HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);  
dataValidation.setSuppressDropDownArrow(false);  

wb.getSheet("sheetname16").addValidationData(dataValidation);

[img]http://dl.iteye.com/upload/attachment/0069/0818/22776f76-f5b8-3052-8d63-7bb5183d2526.gif[/img]

41、设置输入提示信息

CellRangeAddressList addressList = new CellRangeAddressList(  
        0,  
        0,  
        0,  
        0);  

final String[] DATA_LIST = new String[] {  
        "10",  
        "20",  
        "30",  
};  
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);  

HSSFDataValidation dataValidation =  
    new HSSFDataValidation(addressList, dvConstraint);  
dataValidation.setSuppressDropDownArrow(false);  
dataValidation.createPromptBox("输入提示", "请从下拉列表中选择！");  
dataValidation.setShowPromptBox(true);  

wb.getSheet("sheetname17").addValidationData(dataValidation);