使用POI 实现 Excel 导入导出

使用POI 实现 Excel导入导出

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadFromExcel
{
    // 放通脚本
    public static List<String> fangtongList = new ArrayList<String>();
    
    // 回滚脚本
    public static List<String> huigunList = new ArrayList<String>();
    
    /**
     * 读Excel2007
     * <功能详细描述>
     * @param filename
     */
    public static void readExcel2007(String filename)
    {
        System.out.println("ReadFromExcel.readExcel2007()");
        XSSFWorkbook workbook = null;
        InputStream in = null;
        File file = new File(filename);
        try
        {
            in = new FileInputStream(file);
            workbook = new XSSFWorkbook(in);
            
            // 读取sheet1
            XSSFSheet sheet = workbook.getSheetAt(0);
            
            // 取得第一行的表名
            XSSFRow headRow = sheet.getRow(0);
            // 第一列
            int sttCol = headRow.getFirstCellNum();
            // 最后一列
            int endCol = headRow.getPhysicalNumberOfCells();
            if (endCol < 2)
            {
                System.err.println("ERROR:import template file error");
            }
            //System.out.println(sttCol + "\t" + endCol);
            // 第二行，第一行是表头
            int sttLine = sheet.getFirstRowNum() + 1;
            // 最后一行
            int endLine = sheet.getLastRowNum();
            
            // 遍历每一行
            for (int m = sttLine; m <= endLine; m++)
            {
                XSSFRow row = sheet.getRow(m);
                
                if (null == row)
                {
                    System.err.println("ERROR:import template file error");
                    continue;
                }
                
                String[] colvalue = new String[endCol];
                for (int j = sttCol; j < endCol; j++)
                {
                    XSSFCell cell = row.getCell(j);
                    colvalue[j] = cell.getRichStringCellValue().getString();
                    if ("".equals(colvalue[j].trim()))
                    {
                        System.err.println("ERROR:import template file error");
                    }
                }
                fangtongList.add(colvalue[0]);
                huigunList.add(colvalue[1]);
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
            
        }
        finally
        {
            
            if (in != null)
            {
                try
                {
                    in.close();
                    in = null;
                }
                catch (IOException e)
                {
                    System.err.println("ReadFromExcel.readExcel2007() InputStream failed!");
                }
            }
        }
    }
    
    /**
     * 读 Excel2003
     * <功能详细描述>
     * @param filename
     */
    public static void readExcel2003(String filename)
    {
        System.out.println("ReadFromExcel.readExcel2003()");
        HSSFWorkbook workbook = null;
        InputStream in = null;
        File file = new File(filename);
        try
        {
            in = new FileInputStream(file);
            workbook = new HSSFWorkbook(in);
            
            // 读取sheet1
            HSSFSheet sheet = workbook.getSheetAt(0);
            
            // 取得第一行的表名
            HSSFRow headRow = sheet.getRow(0);
            // 第一列
            int sttCol = headRow.getFirstCellNum();
            // 最后一列
            int endCol = headRow.getPhysicalNumberOfCells();
            if (endCol < 2)
            {
                System.err.println("ERROR:import template file error");
            }
            //System.out.println(sttCol + "\t" + endCol);
            // 第二行，第一行是表头
            int sttLine = sheet.getFirstRowNum() + 1;
            // 最后一行
            int endLine = sheet.getLastRowNum();
            
            // 遍历每一行
            for (int m = sttLine; m <= endLine; m++)
            {
                HSSFRow row = sheet.getRow(m);
                
                if (null == row)
                {
                    System.err.println("ERROR:import template file error");
                    continue;
                }
                
                String[] colvalue = new String[endCol];
                for (int j = sttCol; j < endCol; j++)
                {
                    HSSFCell cell = row.getCell(j);
                    colvalue[j] = cell.getRichStringCellValue().getString();
                    if ("".equals(colvalue[j].trim()))
                    {
                        System.err.println("ERROR:import template file error");
                    }
                }
                fangtongList.add(colvalue[0]);
                huigunList.add(colvalue[1]);
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
            
        }
        finally
        {
            
            if (in != null)
            {
                try
                {
                    in.close();
                    in = null;
                }
                catch (IOException e)
                {
                    System.err.println("ReadFromExcel.readExcel2003() InputStream failed!");
                }
            }
        }
    }
    
    //写入Xlsx
    public static void writeXlsx(String fileName, Map<Integer, List<String[]>> map)
    {
        try
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            for (int sheetnum = 0; sheetnum < map.size(); sheetnum++)
            {
                XSSFSheet sheet = wb.createSheet("" + sheetnum);
                List<String[]> list = map.get(sheetnum);
                for (int i = 0; i < list.size(); i++)
                {
                    XSSFRow row = sheet.createRow(i);
                    String[] str = list.get(i);
                    for (int j = 0; j < str.length; j++)
                    {
                        XSSFCell cell = row.createCell(j);
                        cell.setCellValue(str[j]);
                    }
                }
            }
            FileOutputStream outputStream = new FileOutputStream(fileName);
            wb.write(outputStream);
            outputStream.close();
        }
        catch (FileNotFoundException e)
        {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        catch (IOException e)
        {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args)
    {
        ReadFromExcel.readExcel2007("config/01.xlsx");
        System.out.println(ReadFromExcel.fangtongList);
        System.out.println(ReadFromExcel.huigunList);
    }
}

 

POI项目实现的Excel 97文件格式称为HSSF ——Horrible SpreadSheet Format，讨厌的电子表格格式。

HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”.

 

一、通过usermodel读取文件

用HSSF的usermodel读取文件步骤：

1.       创建一个InputStream，InputStream myxls = new FileInputStream(“book.xls”);

2.       创建一个HSSFWorkbook，HSSFWorkbook wb = new HSSFWorkbook(myxls);

3.       由HSSFWorkbook 实例提取工作表，HSSFSheet sheet = wb.getSheetAt(0);

4.       工作表的第三行，HSSFRow row = sheet.getRow(2);

5.       第三行的第四个单元格，HSSFCell cell = row.getCell(3);

6.       单元格对象的数据类型，cell.getCellType() 类型有 HSSFCell.CELL_TYPE_STRING，HSSFCell.CELL_TYPE_NUMERIC

7.       // 第一列

         int sttCol = row.getFirstCellNum();

         // 最后一列

int endCol = row.getPhysicalNumberOfCells();

// 第二行，第一行是表头

int sttLine = sheet.getFirstRowNum()+1;

// 最后一行

int endLine = sheet.getLastRowNum();

HSSFCell cell = row.getCell(j);读取某行的第几列

colvalue[j] = cell.getRichStringCellValue().getString();该单元格的值

 

二、通过usermodel写入文件

创建一个HSSFWorkbook实例，创建一个把文件写入磁盘的OutputStream

HSSFWorkbook wb = new HSSFWorkbook();         FileOutputStream fileOut = new FileOutputStream("workbook.xls");         HSSFSheet sheet = wb.createSheet();         HSSFRow row = sheet.createRow((short)0);         HSSFCell cell = row.createCell((short)0);         cell.setCellValue(1);         row.createCell((short)1).setCellValue(1.2);         row.createCell((short)2).setCellValue("一个字符串值");         row.createCell((short)3).setCellValue(true);         wb.write(fileOut);         fileOut.close();