系列文章目錄

一、Java使用Apache POI導出excel
二、Apache POI 操作Excel常用方法
三、Apache poi 拆分單元格并賦值
四、使用easypoi模板方法導出excel
五、Apache poi給excel單元格添加下拉框或數(shù)據(jù)驗證

一、Apache POI 介紹

POI提供了HSSF、XSSF以及SXSSF三種方式操作Excel。

HSSF：Excel97-2003版本，擴展名為.xls。一個sheet最大行數(shù)65536，最大列數(shù)256。
XSSF：Excel2007版本開始，擴展名為.xlsx。一個sheet最大行數(shù)1048576，最大列數(shù)16384。
SXSSF：是在XSSF基礎上，POI3.8版本開始提供的支持低內(nèi)存占用的操作方式，擴展名為.xlsx。

二、Apache POI中的常用方法

1.新建工作簿和工作表

1、新建工作簿

HSSFWorkbook wb = new HSSFWorkbook();
XSSFWorkbook wb = new XSSFWorkbook();
SXSSFWorkbook wb = new SXSSFWorkbook();

2、新建工作表

HSSFSheet sheet = wb.createSheet("表1");

3、打開一個excel

FileInputStream fileInputStream = new FileInputStream(path);
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);

4、將excel輸出

FileOutputStream fileOutputStream = new FileOutputStream(path);
wb.write(fileOutputStream);
wb.close();
fileOutputStream.close();

2.創(chuàng)建單元格

1、創(chuàng)建行

HSSFRow row = sheet.createRow(0);	// 創(chuàng)建第一行

2、創(chuàng)建列

HSSFCell cell = row.createCell(0);	// 創(chuàng)建第一行的第一個單元格

3.對單元格賦值或取值

1、對單元格賦值

cell.setCellValue("hello world");	// 第一行第一列寫入 hello world
row.createCell(0).setCellValue("hello world");

2、創(chuàng)建不同類型的單元格 （待完善）

CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); 
cell.setCellValue(new Date());	// 設置單元格值（新建日期（））;
cell.setCellValue(Calendar.getInstance()); 設置單元格值（日歷獲取實例（））;

cell.setCellValue(1.1);	// 設置單元格值（1.1）;
cell.setCellValue(new Date());	// 設置單元格值（新建日期（））;
cell.setCellValue(Calendar.getInstance());	// 設置單元格值（日歷.獲取實例（））;
cell.setCellValue("a string");	// 設置單元格值（“字符串”）;
cell.setCellValue(true);	// 設置單元格值（true）;
cell.setCellType(CellType.ERROR);	// 設置單元格類型（單元格類型錯誤）;

3、遍歷行和單元格

for (int j = 0; j < 5; j++) {
    HSSFRow row = sheet.createRow(j);
    for (int i = 0; i < 5; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue("第"+(j+1)+"行"+"第"+(i+1)+"列");
    }
}

4、獲取單元格內(nèi)容

DataFormatter formatter = new DataFormatter();
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());  // 獲取單元格
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");
        String text = formatter.formatCellValue(cell);  // 獲取文本
        System.out.println(text);   // 輸出文本
    }
}

5、文本提取

try (InputStream inp = new FileInputStream("D:\\桌面\\excel.xls")) {
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
    ExcelExtractor extractor = new ExcelExtractor(wb);
    extractor.setFormulasNotResults(true);
    extractor.setIncludeSheetNames(false);
    String text = extractor.getText();
    System.out.println(text);
    wb.close();
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

6、在單元格中使用換行符

cellStyle.setWrapText(true);    //  要啟用換行符，需要使用wrap=true設置單元格樣式

7、對單元格添加下拉框

方法一

String[] values = {"參數(shù)1", "參數(shù)2", "參數(shù)3"};  // 長度不能超過255 否則會報錯
setDropDownBox(sheet, values, 1, 100, 0, 0);

private static void setDropDownBox(
                    XSSFSheet sheet,   // 指定sheet頁
					String[] values,   // 下拉框的值
					Integer firstRow,  // 起始行號
					Integer lastRow,   // 終止行號
					Integer firstCol,  // 起始列號
					Integer lastCol    // 終止列號) {
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(values);
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    //這兩行設置單元格只能是列表中的內(nèi)容，否則報錯
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);
    sheet.addValidationData(validation);
}

方法二

String strFormula = "部門列表!$A$1:$A$10");
setDropDownBoxString(sheet, strFormula, 1, 100, 1, 1);
        
private static void setDropDownBoxString(
		                    XSSFSheet sheet,   // 指定sheet頁
							String values,     // 下拉框的值
							Integer firstRow,  // 起始行號
							Integer lastRow,   // 終止行號
							Integer firstCol,  // 起始列號
							Integer lastCol    // 終止列號) {
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    XSSFDataValidationConstraint dvConstraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,values);
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    //這兩行設置單元格只能是列表中的內(nèi)容，否則報錯
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);
    sheet.addValidationData(validation);
}

4.設置單元格樣式

HSSFCellStyle cellStyle = wb.createCellStyle();
cell.setCellStyle(cellStyle);	// 應用于單元格

1、設置水平對齊

cellStyle.setAlignment(HorizontalAlignment.GENERAL);	// 常規(guī)
cellStyle.setAlignment(HorizontalAlignment.LEFT);	// 左對齊
cellStyle.setAlignment(HorizontalAlignment.CENTER);	// 劇中
cellStyle.setAlignment(HorizontalAlignment.RIGHT);	// 右對齊
cellStyle.setAlignment(HorizontalAlignment.FILL);	// 填充
cellStyle.setAlignment(HorizontalAlignment.JUSTIFY);	// 
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);	// 
cellStyle.setAlignment(HorizontalAlignment.DISTRIBUTED);	// 分布

2、設置垂直對齊

cellStyle.setVerticalAlignment(VerticalAlignment.TOP);	// 頂部對齊
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);	// 居中對齊
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);	// 底部對齊
cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
cellStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);

3、設置邊框顏色及樣式

// 設置邊框顏色
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());	// 設置邊框底部顏色
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());	// 設置邊框頂部顏色
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());	// 設置邊框左部顏色
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());	// 設置邊框右部顏色
// 設置邊框樣式
cellStyle.setBorderTop(BorderStyle.THIN);	// 設置頂部邊框 
cellStyle.setBorderLeft(BorderStyle.THIN);	// 設置左部邊框	
cellStyle.setBorderBottom(BorderStyle.THIN);	// 設置底部邊框
cellStyle.setBorderRight(BorderStyle.THIN);	// 設置右部邊框

邊框顏色對照

邊框樣式對照

4、設置填充顏色及樣式

cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());	// 設置背景顏色
cellStyle.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);	// 設置填充樣式

填充樣式對照

5、合并單元格

sheet.addMergedRegion(new CellRangeAddress(
        5,  // 第一行（從0開始）
        5,  // 最后一行（從0開始）
        1,  // 第一列（從0開始）
        3  // 最后一列（從0開始）
));

5.設置字體樣式

CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
style.setFont(font);
cell.setCellStyle(style);

1、設置字體樣式

font.setFontHeightInPoints((short)24);  // 設置字體大小
font.setFontName("等線"); // 設置字體
font.setItalic(true); // 設置斜體
font.setStrikeout(true);    // 設置刪除線
font.setColor(HSSFColor.RED.index); // 設置字體顏色

2、自定義文字顏色

font.setFontHeightInPoints((short)24);  // 設置字體大小
font.setFontName("等線"); // 設置字體
font.setItalic(true); // 設置斜體
font.setStrikeout(true);    // 設置刪除線
font.setColor(HSSFColor.RED.index); // 設置字體顏色
HSSFPalette palette = wb.getCustomPalette();	// 自定義字體顏色
palette.setColorAtIndex(HSSFColor.RED.index,	
        (byte) 153,    //RGB red (0-255)
        (byte) 100,    //RGB green (0-255)
        (byte) 100     //RGB blue (0-255)
);

5.行、列操作

1、設置行高和列寬

sheet.setColumnWidth(0, (20 * 256));	// 設置列寬（第幾列，寬度）0-65280
sheet.setDefaultRowHeight((short) (2 * 256));	// 設置行高
row.setHeight((short)(2 * 256));	// 設置行高
sheet.autoSizeColumn(0); 	// 自動調(diào)整列大小

2、獲取最大行列數(shù)

int lastRowNum = sheet.getLastRowNum();		// 獲取行數(shù)
row.getLastCellNum();		// 獲取列數(shù)

3、組合行、列

對行或列可以進行展開和合并操作

sheet.groupRow(1,3);	// 組合行
sheet.groupColumn(2,7);	// 組合列
sheet.ungroupColumn(1, 3);	// 取消組合
sheet.ungroupRow(1,3);		// 取消組合

4、凍結(jié)行、列

使行、列不隨滾動條滾動

sheet.createFreezePane( 
	0, 	// 凍結(jié)的行數(shù)
	1, 	// 凍結(jié)的列數(shù)
	0, 	// 右邊區(qū)域[可見]的首列序號
	1 	// 下邊區(qū)域[可見]的首行序號
);

5、隱藏行和取消隱藏行

row.setZeroHeight(true);	// 隱藏行
row.setZeroHeight(false);	// 取消隱藏

6、重復行和列

sheet.setRepeatingRows(CellRangeAddress.valueOf("2:5"));	// 重復行
sheet.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));	// 重復列

6.對工作表操作

1、重命名工作表

wb.createSheet("表1");	// 創(chuàng)建工作表(表1)
wb.setSheetName(0,"更新命名");	// 重命名工作表

2、設置默認工作表

wb.setActiveSheet(0);	// 設置默認工作表

3、設置工作表顯示比例

sheet.setZoom(1,3);		// 1/3比例 （1-65536）

4、獲取表/刪除表

wb.getSheet("表1");		// 獲取表1
wb.getSheetAt(0);		// 獲取第一個工作表
wb.removeSheetAt(0);		// 刪除第一個工作表

5、網(wǎng)格線操作

sheet.setDisplayGridlines(false);	// 隱藏Excel網(wǎng)格線,默認值為true
sheet.setGridsPrinted(true);	// 打印時顯示網(wǎng)格線,默認值為false

6、使工作表適合一頁（待完善）

PrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true); 		// 工作表設置自動換行
ps.setFitHeight((short)1); 		// 設置擬合高度
ps.setFitWidth((short)1); 		// 設置擬合寬度

7、設置工作表打印區(qū)域

wb.setPrintArea(0, "$A$1:$C$2"); 	// 設置打印區(qū)域（第0頁 $A$1至$C$2）
// 或
wb.setPrintArea( 
        0,        //工作表索引
        0,        //開始列
        1,        //結(jié)束列
        0,        //開始行
        0         //結(jié)束行
);

8、設置工作表的頁眉頁腳

HSSFHeader header = sheet.getHeader();
header.setLeft("頁眉左");
header.setRight("頁眉右");
header.setCenter("頁眉中");
HSSFFooter footer = sheet.getFooter();
footer.setLeft("頁腳左");
footer.setRight("頁腳右");
footer.setCenter("頁腳中");

9、拆分窗格

sheet.createSplitPane(
        2000,   // 拆分的水平位置
        2000,   // 拆分的垂直位置
        0,  // 左列在右窗格中可見
        0,  // 頂行在底部窗格中可見
        Sheet.PANE_LOWER_LEFT   // 活動窗格  右下、右上、左下、左上
);

10、移動行

sheet.shiftRows(
        2,  // 起始行
        4,  // 結(jié)束行
        2   // 移動的行數(shù)
);

11、隱藏工作表

wb.setSheetHidden(
			1,     // sheet也index值
			true   // true 隱藏
);

7.插入圖片

插入圖片
HSSFClientAnchor 參數(shù)說明：
dx1 dy1 起始單元格中的x,y坐標.
dx2 dy2 結(jié)束單元格中的x,y坐標
col1,row1 指定起始的單元格，下標從0開始
col2,row2 指定結(jié)束的單元格 ，下標從0開始

FileInputStream stream = new FileInputStream("D:/桌面/logo.png");
byte[] bytes = new byte[(int)stream.getChannel().size()];
stream.read(bytes);   //讀取圖片到二進制數(shù)組
int pictureIdx = wb.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_PNG);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor =new HSSFClientAnchor(0,0,0,0,(short)5,5,(short)7,7);
HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx);
pict.resize();	//自動調(diào)節(jié)圖片大小,圖片位置信息可能丟失

8.其他

1、超鏈接

CreationHelper createHelper = wb.getCreationHelper();
// 關聯(lián)到網(wǎng)站
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("http://poi.apache.org/");
sheet.createRow(0).createCell(0).setHyperlink(link);
// 關聯(lián)到當前目錄的文件
link = createHelper.createHyperlink(HyperlinkType.FILE);
link.setAddress("sample.xls");
sheet.createRow(0).createCell(1).setHyperlink(link);
// e-mail 關聯(lián)
link = createHelper.createHyperlink(HyperlinkType.EMAIL);
link.setAddress("mailto:poi@apache.org?");
sheet.createRow(0).createCell(2).setHyperlink(link);
// 關聯(lián)到工作簿中的位置
link = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
link.setAddress("'Test0'!C3");//Sheet名為Test0的C3位置
sheet.createRow(0).createCell(3).setHyperlink(link);

2、 設置密碼

HSSFRow row=sheet.createRow(1);
HSSFCell cell=row.createCell(1);
cell.setCellValue("已鎖定");
HSSFCellStyle locked = wb.createCellStyle();
locked.setLocked(true);//設置鎖定
cell.setCellStyle(locked);
cell=row.createCell(2);
cell.setCellValue("未鎖定");
HSSFCellStyle unlocked = wb.createCellStyle();
unlocked.setLocked(false);//設置不鎖定
cell.setCellStyle(unlocked);
sheet.protectSheet("password");//設置保護密碼

3、設置批注

HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = patr.createAnchor(0,0,0,0,5,1,8,3);	//創(chuàng)建批注位置
HSSFComment comment = patr.createCellComment(anchor);	//創(chuàng)建批注
comment.setString(new HSSFRichTextString("這是一個批注段落！"));	//設置批注內(nèi)容
comment.setAuthor("Lee");   //設置批注作者
comment.setVisible(true);   //設置批注默認顯示
HSSFCell cell = sheet.createRow(2).createCell(1);
cell.setCellValue("測試");
cell.setCellComment(comment);	//把批注賦值給單元格

4、設置摘要

wb.createInformationProperties();//創(chuàng)建文檔信息
DocumentSummaryInformation dsi= wb.getDocumentSummaryInformation();//摘要信息
dsi.setCategory("類別:Excel文件");//類別
dsi.setManager("管理者:Lee");//管理者
dsi.setCompany("公司");//公司
SummaryInformation si = wb.getSummaryInformation();//摘要信息
si.setSubject("主題");//主題
si.setTitle("標題:測試文檔");//標題
si.setAuthor("作者:Lee");//作者
si.setComments("備注:POI測試文檔");//備注

5、設置下拉框

詳細可見：https://blog.csdn.net/weixin_49832841/article/details/131006589文章來源地址http://www.zghlxwxcb.cn/news/detail-704013.html

setDropDownBox(sheet, values, 1, 1000, 5, 5);
public static void setDropDownBox(XSSFSheet sheet, String[] values, Integer firstRow, Integer lastRow, Integer
		firstCol, Integer lastCol) {
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(values);
	CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
	DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
	//這兩行設置單元格只能是列表中的內(nèi)容，否則報錯
	validation.setSuppressDropDownArrow(true);
	validation.setShowErrorBox(true);
	sheet.addValidationData(validation);
}

setDropDownBoxString(sheet, values, 1, 1000, 10, 10);
public static void setDropDownBoxString(XSSFSheet sheet, String values, Integer firstRow, Integer lastRow, Integer
		firstCol, Integer lastCol) {
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,values);
	CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
	DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
	//這兩行設置單元格只能是列表中的內(nèi)容，否則報錯
	validation.setSuppressDropDownArrow(true);
	validation.setShowErrorBox(true);
	sheet.addValidationData(validation);
}

到了這里，關于Apache POI 操作Excel常用方法的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！