`
\===================寫======================
package com.test;
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
public class PoiCreateExcel {
public static void main(String[] args) throws Exception {
// 創建Excel的工作書冊 Workbook,對應到一個excel文檔
HSSFWorkbook wb = new HSSFWorkbook();
// 創建Excel的工作sheet,對應到一個excel文檔的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 設置excel每列寬度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 創建字體樣式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLUE.index);
// 創建單元格樣式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN\_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);
style.setFillForegroundColor(HSSFColor.LIGHT\_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID\_FOREGROUND);
// 設置邊框
style.setBottomBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER\_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER\_THIN);
style.setBorderRight(HSSFCellStyle.BORDER\_THIN);
style.setBorderTop(HSSFCellStyle.BORDER\_THIN);
style.setFont(font);// 設置字體
// 創建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 設定行的高度
// 創建一個Excel的單元格
HSSFCell cell = row.createCell(0);
// 合併單元格(startRow，endRow，startColumn，endColumn)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
// 給Excel的單元格設置樣式和賦值
cell.setCellStyle(style);
cell.setCellValue("hello world");
// 設置單元格內容格式
HSSFCellStyle style1 = wb.createCellStyle();
style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
style1.setWrapText(true);// 自動換行
row = sheet.createRow(1);
// 設置單元格的樣式格式
cell = row.createCell(0);
cell.setCellStyle(style1);
cell.setCellValue(new Date());
// 創建超鏈接
HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK\_URL);
link.setAddress("http://www.developcls.com");
cell = row.createCell(1);
cell.setCellValue("百度");
cell.setHyperlink(link);// 設定單元格的鏈接
FileOutputStream os = new FileOutputStream("D:\\report\\workbook.xls");
wb.write(os);
os.close();
}
}
\===================讀\===============
package com.test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class PoiTestExcel {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
InputStream in = new FileInputStream("D:\\report\\1111.xls");
Workbook work = new HSSFWorkbook(in);
// 得到excel的第0張表
Sheet sheet = work.getSheetAt(0);
// 得到第1行的第一個單元格的樣式
Row rowCellStyle = sheet.getRow(1);
CellStyle columnOne = rowCellStyle.getCell(0).getCellStyle();
// 這裏面的行和列的數法與計算機裏的一樣，從0開始是第一
// 填充title數據
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellValue("2010年花名測");
int i = 2;//計數器
int number = 0;
// 得到行，並填充數據和表格樣式
for (;i < 10; i++) {
row = sheet.createRow(i);// 得到行
cell = row.createCell(0);// 得到第0個單元格
cell.setCellValue("琳"+i);// 填充值
cell.setCellStyle(columnOne);// 填充樣式
cell = row.createCell(1);
cell.setCellValue("女");
cell.setCellStyle(columnOne);// 填充樣式
cell = row.createCell(2);
cell.setCellValue(i+20);
cell.setCellStyle(columnOne);// 填充樣式
// .....給每個單元格填充數據和樣式
number++;
}
//創建每個單元格，添加樣式，最後合併
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue("總計：" + number + "個學生");// 填充值
cell.setCellStyle(columnOne);
cell = row.createCell(1);
cell.setCellStyle(columnOne);
cell = row.createCell(2);
cell.setCellStyle(columnOne);
// 合併單元格
sheet.addMergedRegion(new CellRangeAddress(i,i,0,2));
FileOutputStream os = new FileOutputStream("D:\\report\\workbook.xls");
work.write(os);
os.close();
} catch (FileNotFoundException e) {
System.out.println("文件路徑錯誤");
e.printStackTrace();
} catch (IOException e) {
System.out.println("文件輸入流錯誤");
e.printStackTrace();
}
}
}
`
更多poi操作excel文章參考：https://www.shangmayuan.com/a/cbf93722e8c44c91ae8d6264.html
thank you!
