excel导入和导出

在开发中表格的导出导入是极为常见的功能

这里介绍poi方式

excel2007使用：org.apache.poi.xssf.usermodel.XSSFWorkbook
excel2003使用：org.apache.poi.hssf.usermodel.HSSFWorkbook

 

一、导入

//获取文件的输入流
File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);

//将输入流传给WorkBook
WorkBook wb = null;
if (fileName.contains("xls")) {
wb = new HSSFWorkBook(fis);
} else {
wb = new XSSFWorkBook(fis);
}

//遍历每个sheet获取数据

for (int k = 0; k < wb.getNumberOfSheets(); k++) {
    HSSFSheet sheet = wb.getSheetAt(k);// 取出第一个工作表，索引是0
    // 开始循环遍历行，表头不处理，从1开始
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        InsProdEntity = new InsProdEntity();// 实例化InsProdEntity对象
        HSSFRow row = sheet.getRow(i);// 获取行对象
        if (row == null) {// 如果为空，不处理
            continue;
        }
        // 循环遍历单元格
        for (int j = 0; j < row.getLastCellNum(); j++) {
            HSSFCell cell = row.getCell(j);// 获取单元格对象
            if (cell == null) {// 单元格为空设置cellStr为空串
                cellStr = "";
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
                cellStr = String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
                cellStr = cell.getNumericCellValue() + "";
            } else {// 其余按照字符串处理
                cellStr = cell.getStringCellValue();
            }
            // 下面按照数据出现位置封装到bean中
            if (j == 0) {
                InsProdEntity.setInsProdID(Integer.parseInt(cellStr));
            } else if (j == 1) {
                InsProdEntity.setInsProdName(cellStr);
            }
        }
        InsProdEntityList.add(InsProdEntity);// 数据装入List
    }
}

 

二、导出

//先获取要导出的数据

List<User> list = new ArrayList<User>();

//得到一个WorkBook

WorkBook wb = null;

wb = new XSSFWorkbook();//或者 new HSSFWorkBook();
//创建sheet;

Sheet sheet = wb.createSheet();

//表头

HSSFRow row = sheet.createRow((int) 0);

HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("编号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("密码");
cell.setCellStyle(style);

//创建数据行

for (int i = 0; i < codeAmount; i++) {

CoupPasswd coupPasswd = new CoupPasswd();

String nextNumValue = Long.toString(com.gc.frame.core.dbobj.NextNumber.getInstance().getNext(coupPasswd.getDBName(), coupPasswd,"CoupPwdID"));
String passWord = GiftNumber.getGiftNumber();

row = sheet.createRow((int) i + 1);
row.createCell((short) 0).setCellValue(nextNumValue);
row.createCell((short) 1).setCellValue(passWord);

}



//输出excel

//到固定目录

FileOutputStream fos = new FileOutputStream("E:\\a.xls");
wb.write(fos);

//或者显示下载样式

response.reset();

response.setHeader("application-disposition","attachment;filename=a.xls");

response.setContentType("application/msexcel");

OutputStream os = response.getOutputStream();

wb.write(os);



os.close();