POI导入导出工作簿

准备：myeclipse，poi-3.9.jar包

过程：创建Dynamic web project，吧jar包拷贝到webroot下的lib文件夹下。之后创建并运行一个java文件。

注意：1，必须是Dynamic web project，其他的有可能不行。

    2.   java 文件运行为Java Application

一、POI输出到工作簿

1.1数据类型样式修改

import java.io.FileOutputStream;import java.util.Calendar;import java.util.Date;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.CreationHelper;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;public class demo4 {public static void main(String[] args) throws Exception {Workbook wb=new HSSFWorkbook();//创建一个工作簿Sheet sheet=wb.createSheet("第一个sheet页");//创建sheet页Row row=sheet.createRow(0);//创建行/** * 以下为创建单元格 */Cell cell=row.createCell(0);//第一个单元格cell.setCellValue(new Date());/** * 日期类格式化 *///创建单元格样式CreationHelper creathelp=wb.getCreationHelper();//小的工具类CellStyle cellstyle=wb.createCellStyle();//单元格样式类cellstyle.setDataFormat(creathelp.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));cell=row.createCell(1);//第2个单元格cell.setCellValue(new Date());cell.setCellStyle(cellstyle);//加载样式cell=row.createCell(2);//第3个单元格cell.setCellValue(Calendar.getInstance());cell.setCellStyle(cellstyle);//加载样式/** * 其他类型的单元格数据 */row.createCell(3).setCellValue(1.2);//创建第一个单元格，值为1.2row.createCell(4).setCellValue("这是一个字符串");//创建第一个单元格，值为row.createCell(5).setCellValue(false);//创建第一个单元格，值为falseFileOutputStream fileOut=new FileOutputStream("E:\\用poi创建时间格式的单元格.xls");wb.write(fileOut);fileOut.close();System.out.println("12341234");}}<strong></strong>

1.2数据显示样式修改

import java.io.FileOutputStream;import java.util.Calendar;import java.util.Date;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRichTextString;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.CreationHelper;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;//单元格样式public class demo6 {public static void main(String[] args) throws Exception {Workbook wb=new HSSFWorkbook();//创建一个工作簿Sheet sheet=wb.createSheet("第一个sheet页");//创建sheet页Row row=sheet.createRow(0);//创建行row.setHeightInPoints(30);creatcell(wb, row, (short)1, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM);creatcell(wb, row, (short)2, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER);creatcell(wb, row, (short)3, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP);creatcell(wb, row, (short)4, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP);FileOutputStream fileOut=new FileOutputStream("E:\\用poi创建时间格式的单元格.xls");wb.write(fileOut);fileOut.close();System.out.println("12341234");}/** * 创建单元格设置对齐方式 * @param wb 工作簿 * @param row 行 * @param column 列 * @param halign 水平对齐方式 * @param valign 竖直对齐方式 */private static void creatcell(Workbook wb,Row row,short column,short halign,short valign){Cell cell=row.createCell(column);cell.setCellValue(new HSSFRichTextString("SB"));CellStyle cellStyle=wb.createCellStyle();cellStyle.setVerticalAlignment(valign);cellStyle.setAlignment(halign);cell.setCellStyle(cellStyle);}}

结果是：

二、POI从工作簿读取

2.1 简单，仅获取文本

excelExtractor.setIncludeSheetNames(false)的作用是设置是否显示sheet页名称。true：显示；false：不显示

import java.io.FileInputStream;import java.io.InputStream;import org.apache.poi.hssf.extractor.ExcelExtractor;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class demo5 {public static void main(String[] args) throws Exception {InputStream is=new FileInputStream("E:\\二货名单.xls");POIFSFileSystem fs=new POIFSFileSystem(is);HSSFWorkbook wb=new HSSFWorkbook(fs);ExcelExtractor excelExtractor=new ExcelExtractor(wb);excelExtractor.setIncludeSheetNames(false);//是否显示sheet页名称System.out.println(excelExtractor.getText());}}

2.2 按单元格获取

package com.ldl.poi;import java.io.FileInputStream;import java.io.InputStream;import org.apache.poi.hssf.extractor.ExcelExtractor;import org.apache.poi.hssf.usermodel.HSSFCell;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.poifs.filesystem.POIFSFileSystem;public class demo7 {public static void main(String[] args) throws Exception {InputStream is=new FileInputStream("E:\\名单.xls");POIFSFileSystem fs=new POIFSFileSystem(is);HSSFWorkbook wb=new HSSFWorkbook(fs);//获取第一个sheet页HSSFSheet hssfSheet=wb.getSheetAt(0);if (hssfSheet==null) {return;}for (int rowNum = 0; rowNum < hssfSheet.getLastRowNum(); rowNum++) {//遍历sheet页中的每一行HSSFRow hssfRow=hssfSheet.getRow(rowNum);if(hssfRow==null) {continue;}for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) {//遍历行中的每个单元格HSSFCell hssfCell=hssfRow.getCell(cellNum);if (hssfCell==null) {continue;}System.out.print(" "+getValue(hssfCell));}System.out.println();}}/** * 把读取的数据类型转成String * @param hssfCell * @return */private static String getValue(HSSFCell hssfCell) {if (hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN) {return String.valueOf(hssfCell.getBooleanCellValue());} else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {return String.valueOf(hssfCell.getNumericCellValue());}else {return String.valueOf(hssfCell.getStringCellValue());}}}