Java之Excel操作 Apache POI

搜鞥都哦 · 发表于 2017-1-7 09:28:33

　　转自：http://blog.csdn.net/vacblog/article/details/8237674
　　下载地址：http://poi.apache.org/
　　Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。

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格式档案的功能。

　　操作：
　　生成Workbook ： HSSFWorkbook wb = new HSSFWorkbook();
　　添加Worksheet（不添加sheet时生成的xls文件打开时会错）：Sheet sheet3 = wb.createSheet("new sheet");
　　保存为Excel文件： out = new FileOutputStream("c:\\text.xls"); wb.write(out);
　　打开Workbook：
　　方法一：
　　 in = new FileInputStream(TEST_WORKBOOK_NAME);
　　 Workbook wb = WorkbookFactory.create(in);
　　方法二：

in = new FileInputStream(TEST_WORKBOOK_NAME);
POIFSFileSystem fs = new POIFSFileSystem(in);
wb = new HSSFWorkbook(fs);

　　打开加密的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();

　　复制Sheet ：wb.cloneSheet(1);
　　修改Sheet名称：wb.setSheetName(i, "SheetName new");
　　删除Sheet： wb.removeSheetAt(1);
　　调整Sheet顺序：

Java代码

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

　　设置当前Sheet： t.setActiveSheet();
　　或: wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);
　　固定窗口 :wb.getSheet("SheetName4").createFreezePane(2, 2);

　　分割窗口
　　wb.getSheet("SheetName5").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

　　Sheet缩放 :

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

　　不显示网格线

Java代码

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

23、设置分页

Java代码

//设置第一页：3行2列（可以多次设置）
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、设置Header，Footer

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("打印时间:" + 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、设置单元格值

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、设置单元格边线

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、设置单元格背景填充

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、设置单元格注释

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、设置单元格字体（斜体，粗体，下线，取消线，字体，大小，背景色）

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

31、设置超链接

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 = 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、设置单元格横向对齐，纵向对齐

Java代码

//横向对齐

wb.getSheet("sheetname7").setColumnWidth(2, 3072);



Row[] row = new Row[7];

Cell[] cell = new Cell[7];



for (int i = 0 ; i < 7 ; i++){

  row = wb.getSheet("sheetname7").createRow(i + 1);

  cell = row.createCell(2);

  cell.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 = row2.createCell(i + 1);

    cell2.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、设置单元格旋转角度

Java代码

Row[] row = new Row[4];

Cell[] cell = new Cell[4];



for (int i = 0 ; i < 4 ; i++){

  row = wb.getSheet("sheetname9").createRow(i + 1);

  cell = row.createCell(2);

  cell.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、设置单元格自动折行

Java代码

Row[] row = new Row[2];

Cell[] cell = new Cell[2];



for (int i = 0 ; i < 2 ; i++){

  row = wb.getSheet("sheetname10").createRow(i + 1);

  cell = row.createCell(2);

  cell.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、设置单元格文字缩进

Java代码

Row[] row = new Row[4];

Cell[] cell = new Cell[4];



for (int i = 0 ; i < 4 ; i++){

  row = wb.getSheet("sheetname11").createRow(i + 1);

  cell = row.createCell(2);

  cell.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 = wb.getSheet("sheetname12").createRow(i + 1);

}

DataFormat format = wb.createDataFormat();



CellStyle[] styles = new CellStyle[2];

for (int i = 0; i < styles.length; i++) {

    styles = 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 = rows.createCell(1);

    cells.setCellValue(1111.25);



    cells.setCellStyle(styles);

}

　　

37、设置公式

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、设置可输入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、设置输入提示信息

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

　　

账号		自动登录	找回密码
密码			立即注册

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

c++ size_t 和 int 的区别

[经验分享] Java之Excel操作 Apache POI

浏览过的版块

扫码加入运维网微信交流群