一、POI概述
Apache POI是Apache軟件基金會(huì)的開(kāi)放源碼函式庫(kù)，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫的功能。
結(jié)構(gòu)：
HSSF － 提供讀寫Microsoft Excel格式檔案的功能。
XSSF － 提供讀寫Microsoft Excel OOXML格式檔案的功能。
HWPF － 提供讀寫Microsoft Word格式檔案的功能。
HSLF － 提供讀寫Microsoft PowerPoint格式檔案的功能。
HDGF － 提供讀寫Microsoft Visio格式檔案的功能。
使用必須引入依賴
org.apache.poi
poi
3.17
注：3.17版本是支持jdk6的最后版本
二、HSSF概況
HSSF 是Horrible SpreadSheet Format的縮寫，通過(guò)HSSF，你可以用純Java代碼來(lái)讀取、寫入、修改Excel文件。HSSF 為讀取操作提供了兩類API：usermodel和eventusermodel，即“用戶模型”和“事件-用戶模型”。
三、 POI EXCEL文檔結(jié)構(gòu)類
HSSFWorkbook excel文檔對(duì)象
HSSFSheet excel的sheet
HSSFRow excel的行
HSSFCell excel的單元格
HSSFFont excel字體
HSSFName 名稱
HSSFDataFormat 日期格式
HSSFHeader sheet頭
HSSFFooter sheet尾
HSSFCellStyle cell樣式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 錯(cuò)誤信息表
四、EXCEL的讀寫操作
1、讀取“區(qū)域數(shù)據(jù).xls”并儲(chǔ)存于list集合中，“區(qū)域數(shù)據(jù).xls”如下圖
public List
importXLS(){
ArrayList
list = new ArrayList<>();
try {
//1、獲取文件輸入流
InputStream inputStream = new FileInputStream("/Users/Shared/區(qū)域數(shù)據(jù).xls");
//2、獲取Excel工作簿對(duì)象
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//3、得到Excel工作表對(duì)象
HSSFSheet sheetAt = workbook.getSheetAt(0);
//4、循環(huán)讀取表格數(shù)據(jù)
for (Row row : sheetAt) {
//首行（即表頭）不讀取
if (row.getRowNum() == 0) {
continue;
}
//讀取當(dāng)前行中單元格數(shù)據(jù)，索引從0開(kāi)始
String areaNum = row.getCell(0).getStringCellValue();
String province = row.getCell(1).getStringCellValue();
String city = row.getCell(2).getStringCellValue();
String district = row.getCell(3).getStringCellValue();
String postcode = row.getCell(4).getStringCellValue();
Area area = new Area();
area.setCity(city);
area.setDistrict(district);
area.setProvince(province);
area.setPostCode(postcode);
list.add(area);
}
//5、關(guān)閉流
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
2、導(dǎo)出數(shù)據(jù)到“區(qū)域數(shù)據(jù).xls”文件中，頁(yè)面數(shù)據(jù)如下圖：
public void exportExcel() throws IOException {
Page
page = areaService.pageQuery(null);
List
list = page.getContent();
//1.在內(nèi)存中創(chuàng)建一個(gè)excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//2.創(chuàng)建工作簿
HSSFSheet sheet = hssfWorkbook.createSheet();
//3.創(chuàng)建標(biāo)題行
HSSFRow titlerRow = sheet.createRow(0);
titlerRow.createCell(0).setCellValue("省");
titlerRow.createCell(1).setCellValue("市");
titlerRow.createCell(2).setCellValue("區(qū)");
titlerRow.createCell(3).setCellValue("郵編");
titlerRow.createCell(4).setCellValue("簡(jiǎn)碼");
titlerRow.createCell(5).setCellValue("城市編碼");
//4.遍歷數(shù)據(jù),創(chuàng)建數(shù)據(jù)行
for (Area area : list) {
//獲取最后一行的行號(hào)
int lastRowNum = sheet.getLastRowNum();
HSSFRow dataRow = sheet.createRow(lastRowNum + 1);
dataRow.createCell(0).setCellValue(area.getProvince());
dataRow.createCell(1).setCellValue(area.getCity());
dataRow.createCell(2).setCellValue(area.getDistrict());
dataRow.createCell(3).setCellValue(area.getPostcode());
dataRow.createCell(4).setCellValue(area.getShortcode());
dataRow.createCell(5).setCellValue(area.getCitycode());
}
//5.創(chuàng)建文件名
String fileName = "區(qū)域數(shù)據(jù)統(tǒng)計(jì).xls";
//6.獲取輸出流對(duì)象
HttpServletResponse response = ServletActionContext.getResponse();
ServletOutputStream outputStream = response.getOutputStream();
//7.獲取mimeType
ServletContext servletContext = ServletActionContext.getServletContext();
String mimeType = servletContext.getMimeType(fileName);
//8.獲取瀏覽器信息,對(duì)文件名進(jìn)行重新編碼
HttpServletRequest request = ServletActionContext.getRequest();
fileName = FileUtils.filenameEncoding(fileName, request);
//9.設(shè)置信息頭
response.setContentType(mimeType);
response.setHeader("Content-Disposition","attachment;filename="+fileName);
//10.寫出文件,關(guān)閉流
hssfWorkbook.write(outputStream);
hssfWorkbook.close();
}
工具類
public class FileUtils {
public static String filenameEncoding(String filename, HttpServletRequest request) throws IOException {
String agent = request.getHeader("User-Agent"); //獲取瀏覽器
if (agent.contains("Firefox")) {
BASE64Encoder base64Encoder = new BASE64Encoder();
filename = "=?utf-8?B?"
+ base64Encoder.encode(filename.getBytes("utf-8"))
+ "?=";
} else if(agent.contains("MSIE")) {
filename = URLEncoder.encode(filename, "utf-8");
} else if(agent.contains ("Safari")) {
filename = new String (filename.getBytes ("utf-8"),"ISO8859-1");
} else {
filename = URLEncoder.encode(filename, "utf-8");
}
return filename;
}
}
寫出xls文件：
五、 EXCEL常用操作方法
1、 得到Excel常用對(duì)象
POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));
//得到Excel工作簿對(duì)象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到Excel工作表對(duì)象
HSSFSheet sheet = wb.getSheetAt(0);
//得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
//得到Excel工作表指定行的單元格
HSSFCell cell = row.getCell((short) j);
cellStyle = cell.getCellStyle();//得到單元格樣式
2、建立Excel常用對(duì)象
HSSFWorkbook wb = new HSSFWorkbook();//創(chuàng)建Excel工作簿對(duì)象
HSSFSheet sheet = wb.createSheet("new sheet");//創(chuàng)建Excel工作表對(duì)象
HSSFRow row = sheet.createRow((short)0); //創(chuàng)建Excel工作表的行
cellStyle = wb.createCellStyle();//創(chuàng)建單元格樣式
row.createCell((short)0).setCellStyle(cellStyle); //創(chuàng)建Excel工作表指定行的單元格
row.createCell((short)0).setCellValue(1); //設(shè)置Excel工作表的值
3、設(shè)置sheet名稱和單元格內(nèi)容
wb.setSheetName(1, "第一張工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("單元格內(nèi)容");
4、取得sheet的數(shù)目
wb.getNumberOfSheets()
5、 根據(jù)index取得sheet對(duì)象
HSSFSheet sheet = wb.getSheetAt(0);
6、取得有效的行數(shù)
int rowcount = sheet.getLastRowNum();
7、取得一行的有效單元格個(gè)數(shù)
row.getLastCellNum();
8、單元格值類型讀寫
cell.setCellType(HSSFCell.CELL_TYPE_STRING); //設(shè)置單元格為STRING類型
cell.getNumericCellValue();//讀取為數(shù)值類型的單元格內(nèi)容
9、設(shè)置列寬、行高
sheet.setColumnWidth((short)column,(short)width);
row.setHeight((short)height);
10、添加區(qū)域，合并單元格
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo
,(short)columnTo);//合并從第rowFrom行columnFrom列
sheet.addMergedRegion(region);// 到rowTo行columnTo的區(qū)域
//得到所有區(qū)域
sheet.getNumMergedRegions()
11、保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
12、根據(jù)單元格不同屬性返回字符串?dāng)?shù)值
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://字符串類型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: //數(shù)值類型
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}
13、常用單元格邊框格式
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下邊框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左邊框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框
14、設(shè)置字體和內(nèi)容位置
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 11);//字號(hào)
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
style.setRotation(short rotation);//單元格內(nèi)容的旋轉(zhuǎn)的角度
HSSFDataFormat df = wb.createDataFormat();
style1.setDataFormat(df.getFormat("0.00%"));//設(shè)置單元格數(shù)據(jù)格式
cell.setCellFormula(string);//給單元格設(shè)公式
style.setRotation(short rotation);//單元格內(nèi)容的旋轉(zhuǎn)的角度
15、插入圖片
//先把讀進(jìn)來(lái)的圖片放到一個(gè)ByteArrayOutputStream中，以便產(chǎn)生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//讀進(jìn)一個(gè)excel模版
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//創(chuàng)建一個(gè)工作薄
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、調(diào)整工作表位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);
1、在學(xué)習(xí)從文件讀取數(shù)據(jù)中，寫了個(gè)示例代碼，讀取不在同一個(gè)目錄的file.txt，運(yùn)行后報(bào)這個(gè)Python OSError: [Errno 22] Invalid argument:錯(cuò)誤：
（1）、首先，在F盤的python_stu中新增了一個(gè)file.txt，同時(shí)在F盤的python_stu文件目錄底下新增一個(gè)file文件夾，里面有個(gè)file_reader.py來(lái)讀取python_stu文件目錄底下的file.txt，代碼分別如下：
file.txt：
測(cè)試
測(cè)試2
測(cè)試3
file_reader.py：
with open('F:\python_stu\file.txt') as file_obj:
contents = file_obj.read();
print(contents.rstrip());
（2）、運(yùn)行后報(bào)錯(cuò)：
（3）、出現(xiàn)這種錯(cuò)誤的原因是由于讀取不到這個(gè)文件，看Traceback報(bào)的錯(cuò)誤，最后一行，很明顯讀取不到file.txt，前面的F:\\python_stu沒(méi)錯(cuò)，后面的名稱怎么變了，還是x0cile.txt。
（4）、解決辦法，可修改上述第一行代碼為：
with open('F:\python_stu/file.txt') as file_obj:
或者：
with open('F:/python_stu/file.txt') as file_obj:
或者：
with open('F://python_stu//file.txt') as file_obj:
又或者：
with open('F:\\python_stu\\file.txt') as file_obj:
還有一些我就不附上了，上面第一種方式不統(tǒng)一，最好不要用，用統(tǒng)一的方式，而且有時(shí)候還有注意一些轉(zhuǎn)義字符，比如 \t，\n也會(huì)導(dǎo)致報(bào)錯(cuò)。