使用POI导入和导出Excel文件

最新推荐文章于 2023-05-31 16:46:04 发布

iteye_19603

最新推荐文章于 2023-05-31 16:46:04 发布

阅读量115

点赞数

文章标签： java

转自:http://www.blogjava.net/caihualin/articles/164724.html

最近做试题导入导出，选用了poi导入和导出excel文件，直接用poi的API感觉代码很混乱，耦合度很高，所以封装了两个底层类。一个是ExcelReader：主要包含读取excel内容的方法；另一个是ExcelWriter：主要包含几个生成excel文件的方法。现贴出来供大家以后参考使用：

1、ExcelWriter.java

package com.eruite.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

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;

/**
 * @author caihua
 */
public class ExcelReader {
 private HSSFWorkbook wb = null;// book [includes sheet]

 private HSSFSheet sheet = null;

 private HSSFRow row = null;

 private int sheetNum = 0; // 第sheetnum个工作表

 private int rowNum = 0;

 private FileInputStream fis = null;

 private File file = null;

 public ExcelReader() {
 }

 public ExcelReader(File file) {
  this.file = file;
 }

 public void setRowNum(int rowNum) {
  this.rowNum = rowNum;
 }

 public void setSheetNum(int sheetNum) {
  this.sheetNum = sheetNum;
 }

 public void setFile(File file) {
  this.file = file;
 }

 /**
  * 读取excel文件获得HSSFWorkbook对象
  */
 public void open() throws IOException {
  fis = new FileInputStream(file);
  wb = new HSSFWorkbook(new POIFSFileSystem(fis));
  fis.close();
 }

 /**
  * 返回sheet表数目
  * 
  * @return int
  */
 public int getSheetCount() {
  int sheetCount = -1;
  sheetCount = wb.getNumberOfSheets();
  return sheetCount;
 }

 /**
  * sheetNum下的记录行数
  * 
  * @return int
  */
 public int getRowCount() {
  if (wb == null)
   System.out.println("=============>WorkBook为空");
  HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
  int rowCount = -1;
  rowCount = sheet.getLastRowNum();
  return rowCount;
 }

 /**
  * 读取指定sheetNum的rowCount
  * 
  * @param sheetNum
  * @return int
  */
 public int getRowCount(int sheetNum) {
  HSSFSheet sheet = wb.getSheetAt(sheetNum);
  int rowCount = -1;
  rowCount = sheet.getLastRowNum();
  return rowCount;
 }

 /**
  * 得到指定行的内容
  * 
  * @param lineNum
  * @return String[]
  */
 public String[] readExcelLine(int lineNum) {
  return readExcelLine(this.sheetNum, lineNum);
 }

 /**
  * 指定工作表和行数的内容
  * 
  * @param sheetNum
  * @param lineNum
  * @return String[]
  */
 public String[] readExcelLine(int sheetNum, int lineNum) {
  if (sheetNum < 0 || lineNum < 0)
   return null;
  String[] strExcelLine = null;
  try {
   sheet = wb.getSheetAt(sheetNum);
   row = sheet.getRow(lineNum);

   int cellCount = row.getLastCellNum();
   strExcelLine = new String[cellCount + 1];
   for (int i = 0; i <= cellCount; i++) {
    strExcelLine[i] = readStringExcelCell(lineNum, i);
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return strExcelLine;
 }

 /**
  * 读取指定列的内容
  * 
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int cellNum) {
  return readStringExcelCell(this.rowNum, cellNum);
 }

 /**
  * 指定行和列编号的内容
  * 
  * @param rowNum
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int rowNum, int cellNum) {
  return readStringExcelCell(this.sheetNum, rowNum, cellNum);
 }

 /**
  * 指定工作表、行、列下的内容
  * 
  * @param sheetNum
  * @param rowNum
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
  if (sheetNum < 0 || rowNum < 0)
   return "";
  String strExcelCell = "";
  try {
   sheet = wb.getSheetAt(sheetNum);
   row = sheet.getRow(rowNum);

   if (row.getCell((short) cellNum) != null) { // add this condition
    // judge
    switch (row.getCell((short) cellNum).getCellType()) {
    case HSSFCell.CELL_TYPE_FORMULA:
     strExcelCell = "FORMULA ";
     break;
    case HSSFCell.CELL_TYPE_NUMERIC: {
     strExcelCell = String.valueOf(row.getCell((short) cellNum)
       .getNumericCellValue());
    }
     break;
    case HSSFCell.CELL_TYPE_STRING:
     strExcelCell = row.getCell((short) cellNum)
       .getStringCellValue();
     break;
    case HSSFCell.CELL_TYPE_BLANK:
     strExcelCell = "";
     break;
    default:
     strExcelCell = "";
     break;
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return strExcelCell;
 }

 public static void main(String args[]) {
  File file = new File("C:\\qt.xls");
  ExcelReader readExcel = new ExcelReader(file);
  try {
   readExcel.open();
  } catch (IOException e) {
   e.printStackTrace();
  }
  readExcel.setSheetNum(0); // 设置读取索引为0的工作表
  // 总行数
  int count = readExcel.getRowCount();
  for (int i = 0; i <= count; i++) {
   String[] rows = readExcel.readExcelLine(i);
   for (int j = 0; j < rows.length; j++) {
    System.out.print(rows[j] + " ");
   }
   System.out.print("\n");
  }
 }
}

2、ExcelWriter.java

package com.eruite.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 生成导出Excel文件对象
 * 
 * @author caihua
 * 
 */
public class ExcelWriter {
 // 设置cell编码解决中文高位字节截断
 private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;

 // 定制浮点数格式
 private static String NUMBER_FORMAT = "#,##0.00";

 // 定制日期格式
 private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"

 private OutputStream out = null;

 private HSSFWorkbook workbook = null;

 private HSSFSheet sheet = null;

 private HSSFRow row = null;

 public ExcelWriter() {
 }

 /**
  * 初始化Excel
  * 
  */
 public ExcelWriter(OutputStream out) {
  this.out = out;
  this.workbook = new HSSFWorkbook();
  this.sheet = workbook.createSheet();
 }

 /**
  * 导出Excel文件
  * 
  * @throws IOException
  */
 public void export() throws FileNotFoundException, IOException {
  try {
   workbook.write(out);
   out.flush();
   out.close();
  } catch (FileNotFoundException e) {
   throw new IOException(" 生成导出Excel文件出错! ", e);
  } catch (IOException e) {
   throw new IOException(" 写入Excel文件出错! ", e);
  }

 }

 /**
  * 增加一行
  * 
  * @param index
  *            行号
  */
 public void createRow(int index) {
  this.row = this.sheet.createRow(index);
 }

 /**
  * 获取单元格的值
  * 
  * @param index
  *            列号
  */
 public String getCell(int index) {
  HSSFCell cell = this.row.getCell((short) index);
  String strExcelCell = "";
  if (cell != null) { // add this condition
   // judge
   switch (cell.getCellType()) {
   case HSSFCell.CELL_TYPE_FORMULA:
    strExcelCell = "FORMULA ";
    break;
   case HSSFCell.CELL_TYPE_NUMERIC: {
    strExcelCell = String.valueOf(cell.getNumericCellValue());
   }
    break;
   case HSSFCell.CELL_TYPE_STRING:
    strExcelCell = cell.getStringCellValue();
    break;
   case HSSFCell.CELL_TYPE_BLANK:
    strExcelCell = "";
    break;
   default:
    strExcelCell = "";
    break;
   }
  }
  return strExcelCell;
 }

 /**
  * 设置单元格
  * 
  * @param index
  *            列号
  * @param value
  *            单元格填充值
  */
 public void setCell(int index, int value) {
  HSSFCell cell = this.row.createCell((short) index);
  cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
  cell.setCellValue(value);
 }

 /**
  * 设置单元格
  * 
  * @param index
  *            列号
  * @param value
  *            单元格填充值
  */
 public void setCell(int index, double value) {
  HSSFCell cell = this.row.createCell((short) index);
  cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
  cell.setCellValue(value);
  HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
  HSSFDataFormat format = workbook.createDataFormat();
  cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
  cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
 }

 /**
  * 设置单元格
  * 
  * @param index
  *            列号
  * @param value
  *            单元格填充值
  */
 public void setCell(int index, String value) {
  HSSFCell cell = this.row.createCell((short) index);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  cell.setEncoding(XLS_ENCODING);
  cell.setCellValue(value);
 }

 /**
  * 设置单元格
  * 
  * @param index
  *            列号
  * @param value
  *            单元格填充值
  */
 public void setCell(int index, Calendar value) {
  HSSFCell cell = this.row.createCell((short) index);
  cell.setEncoding(XLS_ENCODING);
  cell.setCellValue(value.getTime());
  HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
  cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
  cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
 }

 public static void main(String[] args) {
  System.out.println(" 开始导出Excel文件 ");

  File f = new File("C:\\qt.xls");
  ExcelWriter e = new ExcelWriter();

  try {
   e = new ExcelWriter(new FileOutputStream(f));
  } catch (FileNotFoundException e1) {
   e1.printStackTrace();
  }

  e.createRow(0);
  e.setCell(0, "试题编码 ");
  e.setCell(1, "题型");
  e.setCell(2, "分值");
  e.setCell(3, "难度");
  e.setCell(4, "级别");
  e.setCell(5, "知识点");

  e.createRow(1);
  e.setCell(0, "t1");
  e.setCell(1, 1);
  e.setCell(2, 3.0);
  e.setCell(3, 1);
  e.setCell(4, "重要");
  e.setCell(5, "专业");

  try {
   e.export();
   System.out.println(" 导出Excel文件[成功] ");
  } catch (IOException ex) {
   System.out.println(" 导出Excel文件[失败] ");
   ex.printStackTrace();
  }
 }

}

===========================================================================

多个sheet输出

===========================================================================

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;

/** 
 * 导出EXCEL工具类，适用于单行表头的表格 
 * @author wjq 
 * @since 2012-08-30 
 */  
public class ExportExcelUtils {  
      
private static final long serialVersionUID = 2165773254718823136L;  
    
    /** 
     * @Title: exportExcel 
     * @Description: 导出Excel的方法
     * @author: EX-WANGJIANQIANG001 @ 2012-8-31 下午03:49:08
     * @param workbook 
     * @param sheetNum 
     * @param sheetTitle
     * @param headers
     * @param result
     * @param out
     * @throws Exception    
     */    
    public void exportExcel(HSSFWorkbook workbook,int sheetNum,String sheetTitle, String[] headers, List<List<String>> result, OutputStream out) throws Exception{  
        // 生成一个表格  
    HSSFSheet sheet = workbook.createSheet();
    workbook.setSheetName(sheetNum,sheetTitle);//, HSSFWorkbook.ENCODING_UTF_16
        // 设置表格默认列宽度为20个字节  
    sheet.setDefaultColumnWidth((short) 20);  
        // 生成一个样式  
        HSSFCellStyle style = workbook.createCellStyle();  
        // 设置这些样式  
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.BLACK.index);  
        font.setFontHeightInPoints((short) 12);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // 把字体应用到当前的样式  
        style.setFont(font);  
          
        // 指定当单元格内容显示不下时自动换行  
        style.setWrapText(true);  
          
        // 产生表格标题行  
        HSSFRow row = sheet.createRow(0);  
        for (int i = 0; i < headers.length; i++) {  
            HSSFCell cell = row.createCell((short) i);  
            //==>cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text.toString());
        }  
        // 遍历集合数据，产生数据行  
        if(result != null){  
            int index = 1;  
            for(List<String> m:result){  
                row = sheet.createRow(index);  
                int cellIndex = 0;  
               for(String str:m){
                HSSFCell cell = row.createCell((short) cellIndex);  
                //==>cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                cell.setCellValue(str.toString());  
                cellIndex++;  
              }
              index++;  
            }     
        }  
    }  
    
    
    public static void main(String[] args) {
    	try {  
    	OutputStream out = new FileOutputStream("D:\\test.xls");
    	  List<List<String>> data = new ArrayList<List<String>>();  
    	  List<List<String>> data2 = new ArrayList<List<String>>();  
    	  
    	  for(int i=1;i<5;i++){
    	  List rowData = new ArrayList();
    	  rowData.add(String.valueOf(i));
    	  rowData.add("王宝强");  
    	  data.add(rowData);
    	  }
    	  
    	  for(int i=1;i<5;i++){
    	  List rowData = new ArrayList();
    	  rowData.add(String.valueOf(i));
    	  rowData.add("1111");  
    	  data2.add(rowData);
    	  }
    	  
    	          String[] headers = {"ID", "用户名"};  
    	          ExportExcelUtils eeu = new ExportExcelUtils();
    	          HSSFWorkbook workbook = new HSSFWorkbook();
    	          eeu.exportExcel(workbook,0,"上海", headers, data, out);  
    	          eeu.exportExcel(workbook,1,"北京", headers, data2, out); 
    	          eeu.exportExcel(workbook,2,"深圳", headers, data, out);  
    	          eeu.exportExcel(workbook,3,"广州", headers, data, out);  
    	          workbook.write(out);  
    	          out.close(); 
    	       } catch (Exception e) {  
    	          e.printStackTrace();  
    	       }  
    	   }  
    
}