使用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();
|
