Apache POI

Apache POI 是用Java編寫的免費(fèi)開源的跨平臺的 Java API，Apache POI提供API給Java程式對Microsoft Office格式檔案讀和寫的功能芬位。

Apache POI 是創(chuàng)建和維護(hù)操作各種符合Office Open XML（OOXML）標(biāo)準(zhǔn)和微軟的OLE 2復(fù)合文檔格式（OLE2）的Java API穗椅。用它可以使用Java讀取和創(chuàng)建,修改MS Excel文件.而且,還可以使用Java讀取和創(chuàng)建MS Word和MSPowerPoint文件吁恍。Apache POI 提供Java操作Excel解決方案（適用于Excel97-2008）胰舆。如果處理.xlsx册舞、docx蕴掏、pptx的話可以試試Docx4j 。 Docx4j is a Java library for creating and manipulating Microsoft Open XML (Word docx, Powerpoint pptx, and Excel xlsx) files. 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格式檔案的功能。

http://poi.apache.org/ Busy Developers' Guide to HSSF and XSSF Features 版本：poi-3.8-20120326.jar 1逗嫡、生成Workbook Java代碼

收藏代碼

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

//添加Worksheet（不添加sheet時(shí)生成的xls文件打開時(shí)會報(bào)錯(cuò)）
@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) Java代碼

收藏代碼

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

//......

3、打開Workbook Java代碼

收藏代碼

//方法一：使用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());
}
}

4驱证、打開加密的Workbook(讀加密) Java代碼

收藏代碼

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 Java代碼

收藏代碼

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

6、復(fù)制Sheet Java代碼

收藏代碼

wb.cloneSheet(1);

7抹锄、修改Sheet名稱 Java代碼

收藏代碼

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

8逆瑞、刪除Sheet Java代碼

收藏代碼

wb.removeSheetAt(1);

9、設(shè)置下部Sheet名的Tab的第一個(gè)可見Tab Java代碼

收藏代碼

//設(shè)置下部Sheet名的Tab的第一個(gè)可見Tab（以左的Sheet看不見）
wb.setFirstVisibleTab(2);

10祈远、調(diào)整Sheet順序 Java代碼

收藏代碼

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

11呆万、設(shè)置當(dāng)前Sheet t.setActiveSheet(); Java代碼

收藏代碼

//設(shè)置當(dāng)前Sheet
wb.setActiveSheet(wb.getNumberOfSheets() - 1);
//（Excel的當(dāng)前Sheet被設(shè)置，需要結(jié)合setSelected使用车份，不然下部Sheet名的Tab還是默認(rèn)為第一個(gè)）
//（需要選擇多個(gè)Sheet的話谋减，每個(gè)Sheet調(diào)用setSelected(true)即可）
wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);

12、固定窗口 Java代碼

收藏代碼

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

13扫沼、分割窗口 Java代碼

收藏代碼

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

14出爹、Sheet縮放 Java代碼

收藏代碼

//setZoom(int numerator, int denominator)
//"numerator"÷"denominator" 例如： 3÷1＝3 那就是設(shè)置為300%

//擴(kuò)大(200%)
wb.getSheet("sheetname1").setZoom(2, 1);
//縮小(50%)
wb.getSheet("sheetname2").setZoom(1, 2);

15、行列分組 Java代碼

收藏代碼

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);

點(diǎn)擊查看原始大小圖片

16缎除、關(guān)閉分組 Java代碼

收藏代碼

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

17严就、插入行 Java代碼

收藏代碼

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、刪除行 Java代碼

收藏代碼

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

19器罐、移動行 Java代碼

收藏代碼

//******移動行只移動內(nèi)容梢为，不牽扯行的刪除和插入

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

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

20、修改行高 Java代碼

收藏代碼

//設(shè)置默認(rèn)行高
wb.getSheet("sheetname6").setDefaultRowHeight((short)100);

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

21轰坊、修改列寬 Java代碼

收藏代碼

//設(shè)置默認(rèn)列寬
wb.getSheet("sheetname7").setDefaultColumnWidth(12);

//設(shè)置列寬
wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);

22铸董、不顯示網(wǎng)格線 Java代碼

收藏代碼

//不顯示網(wǎng)格線
wb.getSheet("sheetname8").setDisplayGridlines(false);

23、設(shè)置分頁 Java代碼

收藏代碼

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

24肴沫、添加粟害，刪除，合并單元格 Java代碼

收藏代碼

//追加行
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));

25颤芬、設(shè)置Header悲幅，F(xiàn)ooter Java代碼

收藏代碼

//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("打印時(shí)間:" + 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());

26套鹅、設(shè)置單元格值 Java代碼

收藏代碼

//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);

27、設(shè)置單元格邊線 Java代碼

收藏代碼

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);

28汰具、設(shè)置單元格背景填充 Java代碼

收藏代碼

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);

29卓鹿、設(shè)置單元格注釋 Java代碼

收藏代碼

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、設(shè)置單元格字體（斜體郁副，粗體减牺，下線豌习，取消線存谎，字體，大小肥隆，背景色） Java代碼

收藏代碼

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);

//上標(biāo)
font = wb.createFont();
font.setTypeOffset(HSSFFont.SS_SUPER);
style = wb.createCellStyle();
style.setFont(font);

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

//下標(biāo)
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);

31既荚、設(shè)置超鏈接 Java代碼

收藏代碼

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內(nèi)
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、設(shè)置單元格橫向?qū)R栋艳，縱向?qū)R Java代碼

收藏代碼

//橫向?qū)R
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);

//縱向?qū)R
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);

33恰聘、設(shè)置單元格旋轉(zhuǎn)角度 Java代碼

收藏代碼

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);

34、設(shè)置單元格自動折行 Java代碼

收藏代碼

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);

35吸占、設(shè)置單元格文字縮進(jìn) Java代碼

收藏代碼

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);

36晴叨、自定義格式 Java代碼

收藏代碼

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]);

}

37、設(shè)置公式 Java代碼

收藏代碼

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)");

38矾屯、畫直線兼蕊，圓圈（橢圓），正方形（長方形）,Textbox Java代碼

收藏代碼

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件蚕、插入圖片 Java代碼

收藏代碼

//需要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();

40孙技、設(shè)置可輸入List Java代碼

收藏代碼

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);

41、設(shè)置輸入提示信息 Java代碼

收藏代碼

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);

最后編輯于：2017.12.11 16:21:28

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末牵啦，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子妄痪，更是在濱河造成了極大的恐慌哈雏，老刑警劉巖，帶你破解...
沈念sama閱讀 218,755評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件衫生，死亡現(xiàn)場離奇詭異裳瘪，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)障簿，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,305評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門盹愚，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人站故，你說我怎么就攤上這事皆怕∫阌撸” “怎么了？”我有些...
開封第一講書人閱讀 165,138評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵愈腾，是天一觀的道長憋活。經(jīng)常有香客問我，道長虱黄，這世上最難降的妖魔是什么悦即？我笑而不...
開封第一講書人閱讀 58,791評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮橱乱，結(jié)果婚禮上辜梳，老公的妹妹穿的比我還像新娘。我一直安慰自己泳叠，他們只是感情好作瞄，可當(dāng)我...
茶點(diǎn)故事閱讀 67,794評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著危纫，像睡著了一般宗挥。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上种蝶，一...
開封第一講書人閱讀 51,631評論 1贊 305
城市分裂傳說
那天契耿，我揣著相機(jī)與錄音，去河邊找鬼螃征。笑死搪桂，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的会傲。我是一名探鬼主播锅棕，決...
沈念sama閱讀 40,362評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼淌山！你這毒婦竟也來了裸燎？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,264評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤泼疑，失蹤者是張志新（化名）和其女友劉穎德绿，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體退渗，經(jīng)...
沈念sama閱讀 45,724評論 1贊 315
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡移稳，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,900評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了会油。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片个粱。...
茶點(diǎn)故事閱讀 40,040評論 1贊 350
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖翻翩，靈堂內(nèi)的尸體忽然破棺而出都许，到底是詐尸還是另有隱情稻薇，我是刑警寧澤，帶...
沈念sama閱讀 35,742評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布胶征，位于F島的核電站塞椎，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏睛低。R本人自食惡果不足惜案狠，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,364評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望钱雷。院中可真熱鬧骂铁，春花似錦、人聲如沸急波。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,944評論 0贊 22
一樁弒父案瘪校，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽澄暮。三九已至，卻和暖如春阱扬，著一層夾襖步出監(jiān)牢的瞬間泣懊，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,060評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工麻惶，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留馍刮，地道東北人。一個(gè)月前我還...
沈念sama閱讀 48,247評論 3贊 371
代替公主和親
正文我出身青樓窃蹋，卻偏偏與公主長得像卡啰，于是被迫代替她去往敵國和親。傳聞我的和親對象是個(gè)殘疾皇子警没，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,979評論 2贊 355

Apache POI

推薦閱讀更多精彩內(nèi)容