Java HSSFWorkbook 生成 excel
435 views
0
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格式档案的功能。
Busy Developers' Guide to HSSF and XSSF Features
版本：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);
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);
将jxl.jar放入项目中的lib目录下
Java HSSFWorkbook 生成 excel
public class CwFundsChangeExportExcel {
private HSSFWorkbook wb = new HSSFWorkbook();
private HSSFSheet sheet = wb.createSheet();
public static void main(String[] args) {}
/**
* @param wb
* @param type
* 1:head 2 je
* @return
*/
public HSSFCellStyle setCellStyleHead(HSSFWorkbook wb,int type,int statu)
{
HSSFCellStyle cellStyle = wb.createCellStyle();
// 创建单元格样式
if(type != 2)
{
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
}
else
{
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
//设置单元格边框和颜色
cellStyle.setBorderBottom((short)1);
cellStyle.setBorderLeft((short)1);
cellStyle.setBorderRight((short)1);
cellStyle.setBorderTop((short)1);
HSSFFont font = wb.createFont();
if(type == 1)
{
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
if(statu == 2)
{
font.setColor(HSSFColor.RED.index);
}
font.setFontName("宋体");
cellStyle.setFont(font);
//字体大小
//font.setFontHeight((short) 200);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
return cellStyle;
}
@SuppressWarnings("unchecked")
public void createExcel(Map<String,String> headMap,List<Map<String,Object>> valueList,String url) {
ExportExcel exportExcel = new ExportExcel(wb, sheet);
// 设置列头
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell_00 = row0.createCell((short)0); // 一列
cell_00.setCellStyle(setCellStyleHead(wb,1,0)); // 列头样式
cell_00.setCellValue(new HSSFRichTextString("银行")); // VALUE
HSSFCell cell_01 = row0.createCell((short)1); // 二列
cell_01.setCellStyle(setCellStyleHead(wb,1,0)); // 列头样式
cell_01.setCellValue(new HSSFRichTextString("期初")); // VALUE
HSSFCell cell_02 = row0.createCell((short)2);
cell_02.setCellStyle(setCellStyleHead(wb,1,0));
cell_02.setCellValue(new HSSFRichTextString());
HSSFCell cell_03 = row0.createCell((short)3);
cell_03.setCellStyle(setCellStyleHead(wb,1,0));
cell_03.setCellValue(new HSSFRichTextString("收入"));
HSSFCell cell_04 = row0.createCell((short)4);
cell_04.setCellStyle(setCellStyleHead(wb,1,0));
cell_04.setCellValue(new HSSFRichTextString());
HSSFCell cell_05 = row0.createCell((short)5);
cell_05.setCellStyle(setCellStyleHead(wb,1,0));
cell_05.setCellValue(new HSSFRichTextString());
HSSFCell cell_06 = row0.createCell((short)6);
cell_06.setCellStyle(setCellStyleHead(wb,1,0));
cell_06.setCellValue(new HSSFRichTextString());
HSSFCell cell_07 = row0.createCell((short)7);
cell_07.setCellStyle(setCellStyleHead(wb,1,0));
cell_07.setCellValue(new HSSFRichTextString("支出"));
HSSFCell cell_08 = row0.createCell((short)8);
cell_08.setCellStyle(setCellStyleHead(wb,1,0));
cell_08.setCellValue(new HSSFRichTextString());
HSSFCell cell_09 = row0.createCell((short)9);
cell_09.setCellStyle(setCellStyleHead(wb,1,0));
cell_09.setCellValue(new HSSFRichTextString());
HSSFCell cell_010 = row0.createCell((short)10);
cell_010.setCellStyle(setCellStyleHead(wb,1,0));
cell_010.setCellValue(new HSSFRichTextString());
HSSFCell cell_011 = row0.createCell((short)11);
cell_011.setCellStyle(setCellStyleHead(wb,1,0));
cell_011.setCellValue(new HSSFRichTextString("转款"));
HSSFCell cell_012 = row0.createCell((short)12);
cell_012.setCellStyle(setCellStyleHead(wb,1,0));
cell_012.setCellValue(new HSSFRichTextString("期末"));
HSSFCell cell_013 = row0.createCell((short)13);
cell_013.setCellStyle(setCellStyleHead(wb,1,0));
cell_013.setCellValue(new HSSFRichTextString());
/** 列头 第二行 */
// 设置列头
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell_10 = row1.createCell((short)0); // 一列
cell_10.setCellStyle(setCellStyleHead(wb,1,0)); // 列头样式
cell_10.setCellValue(new HSSFRichTextString()); // VALUE
HSSFCell cell_11 = row1.createCell((short)1); // 二列
cell_11.setCellStyle(setCellStyleHead(wb,1,0)); // 列头样式
cell_11.setCellValue(new HSSFRichTextString("活期")); // VALUE
HSSFCell cell_12 = row1.createCell((short)2);
cell_12.setCellStyle(setCellStyleHead(wb,1,0));
cell_12.setCellValue(new HSSFRichTextString("保证金"));
HSSFCell cell_13 = row1.createCell((short)3);
cell_13.setCellStyle(setCellStyleHead(wb,1,0));
cell_13.setCellValue(new HSSFRichTextString("托收/贴现"));
HSSFCell cell_14 = row1.createCell((short)4);
cell_14.setCellStyle(setCellStyleHead(wb,1,0));
cell_14.setCellValue(new HSSFRichTextString("往来"));
HSSFCell cell_15 = row1.createCell((short)5);
cell_15.setCellStyle(setCellStyleHead(wb,1,0));
cell_15.setCellValue(new HSSFRichTextString("其他"));
HSSFCell cell_16 = row1.createCell((short)6);
cell_16.setCellStyle(setCellStyleHead(wb,1,0));
cell_16.setCellValue(new HSSFRichTextString("收入合计"));
HSSFCell cell_17 = row1.createCell((short)7);
cell_17.setCellStyle(setCellStyleHead(wb,1,0));
cell_17.setCellValue(new HSSFRichTextString("兑付"));
HSSFCell cell_18 = row1.createCell((short)8);
cell_18.setCellStyle(setCellStyleHead(wb,1,0));
cell_18.setCellValue(new HSSFRichTextString("往来"));
HSSFCell cell_19 = row1.createCell((short)9);
cell_19.setCellStyle(setCellStyleHead(wb,1,0));
cell_19.setCellValue(new HSSFRichTextString("其他"));
HSSFCell cell_110 = row1.createCell((short)10);
cell_110.setCellStyle(setCellStyleHead(wb,1,0));
cell_110.setCellValue(new HSSFRichTextString("支出合计"));
HSSFCell cell_111 = row1.createCell((short)11);
cell_111.setCellStyle(setCellStyleHead(wb,1,0));
cell_111.setCellValue(new HSSFRichTextString());
HSSFCell cell_112 = row1.createCell((short)12);
cell_112.setCellStyle(setCellStyleHead(wb,1,0));
cell_112.setCellValue(new HSSFRichTextString("活期"));
HSSFCell cell_113 = row1.createCell((short)13);
cell_113.setCellStyle(setCellStyleHead(wb,1,0));
cell_113.setCellValue(new HSSFRichTextString("保证金"));
sheet.addMergedRegion(new Region(0, (short)0, 1, (short) 0)); // 银行名称 合并单元格
sheet.addMergedRegion(new Region(0, (short)1, 0, (short) 2)); // 期初合并单元格
sheet.addMergedRegion(new Region(0, (short)3, 0, (short) 6)); // 收入合并单元格
sheet.addMergedRegion(new Region(0, (short)7, 0, (short) 10)); // 支出合并单元格
sheet.addMergedRegion(new Region(0, (short)11, 1, (short) 11)); // 转款合并单元格
sheet.addMergedRegion(new Region(0, (short)12, 0, (short) 13)); // 期末合并单元格
// 遍历value值
for (int i = 0 ; i < valueList.size(); i++)
{
HSSFRow _row = sheet.createRow(i+2);
Map<String,Object> valMap = valueList.get(i);
Iterator itHead = headMap.keySet().iterator();
String head = "";
int j = 0;
while(itHead.hasNext())
{
head = itHead.next() + "";
// 日期类型
if(head.indexOf("dt") != -1)
{
HSSFCell cell12 = _row.createCell((short) j);
cell12.setCellValue(new HSSFRichTextString(valMap.get(head)+""));
cell12.setCellStyle(setCellStyleHead(wb,3,0));
}
else if(head.indexOf("sl") != -1)
{
HSSFCell cell12 = _row.createCell((short) j);
int statu = 0;
if(null == valMap.get(head) || "".equals(valMap.get(head)+""))
{
cell12.setCellValue(new HSSFRichTextString());
}
else
{
if((valMap.get(head)+"").indexOf("-")!=-1)
{
statu = 2;
}
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatNumber(valMap.get(head)+"",2)));
}
cell12.setCellStyle(setCellStyleHead(wb,2,statu));
}
else if(head.indexOf("je") != -1)
{
// 金额格式化
HSSFCell cell12 = _row.createCell((short) j);
int statu = 0;
BigDecimal je = new BigDecimal(valMap.get(head)+"");
// 最后一行合计 不用处理
if(i != valueList.size()-1)
{
if(head.equals("tsjes_je") || head.equals("wljes_je") || head.equals("otherjes_je")
|| head.equals("sumjes_je") || head.equals("cdjez_je") || head.equals("wljez_je")
|| head.equals("otherjez_je") || head.equals("sumjez_je") || head.equals("sumzk_je"))
{
if(je.compareTo(new BigDecimal("0")) == 0)
{
cell12.setCellValue(new HSSFRichTextString());
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
if((valMap.get(head)+"").indexOf("-") != -1)
{
statu = 2;
}
cell12.setCellStyle(setCellStyleHead(wb,2,statu));
}
else
{
HSSFCell cell = _row.createCell((short)j);
cell.setCellStyle(setCellStyleHead(wb,3,0)); // 列头样式
cell.setCellValue(new HSSFRichTextString(valMap.get(head)+"")); // VALUE
}
j++;
}
}
for (int i = 0; i < headMap.size(); i++) {
//自适应宽度
sheet.autoSizeColumn((short)i);
//sheet.setColumnWidth((short)i, (short)3000);
}
exportExcel.outputExcel(url);
}
}
参考推荐：
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2017-08-26 01:00:05
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！