Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
接口:
HSSF - Microsoft Excel
XSSF - Microsoft Excel OOXML(07以上版本)
HWPF - Microsoft Word
HSLF - Microsoft PowerPoint
HDGF - Microsoft Visio
EXCEL 结构:
HSSFWorkbook excell文档对象
HSSFSheet excell的表单
HSSFRow excell的行
HSSFCell excell的格子单元
HSSFFont excell字体
HSSFName 名称
HSSFDataFormat 日期格式
读Excel
读取Excel文件时,首先生成一个POIFSFileSystem对象,由POIFSFileSystem对象构造一个HSSFWorkbook,该HSSFWorkbook对象就代表了Excel文档
代码:
FileInputStream is = new FileInputStream("test.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell((short) 0);
String msg = cell.getStringCellValue();
写Excel
FileInputStream is = new FileInputStream("test.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell((short)0);
cell.setCellValue("a test");
FileOutputStream os = new FileOutputStream("test.xls ");
wb.write(os);
os.close();
设置行高、列宽
sheet.setColumnWidth(0, 10000);
row.setHeight((short) 1000);
设置字号、颜色和内容位置
HSSFCellStyle style = wb.createCellStyle();
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 14);
f.setColor(HSSFFont.COLOR_RED);
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
合并单元格
sheet.addMergedRegion(new Region(1, (short) 1, 2, (short) 2));