java之POI读写

最新推荐文章于 2024-03-21 05:24:02 发布

啊锴

最新推荐文章于 2024-03-21 05:24:02 发布

阅读量312

点赞数

分类专栏： java 文章标签： poi java restful

本文链接：https://blog.csdn.net/sqk_IT/article/details/103682611

版权

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Java读写Excel之POI

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）。

如果处理.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格式档案的功能。

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

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

11、设置当前Sheet

//设置当前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);

在这里插入图片描述

13、分割窗口

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

23、设置分页

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

啊锴

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
java之POI读写

Java读写Excel之POI1、生成Workbook2、生成Workbook OOXML形式(.xlsx)3、打开Workbook4、打开加密的Workbook(读加密)5、追加Sheet6、复制Sheet7、修改Sheet名称8、删除Sheet9、设置下部Sheet名的Tab的第一个可见Tab10、调整Sheet顺序11、设置当前Sheet11、设置当前Sheet12、固定窗口13、分割窗口1...
复制链接

扫一扫