java读写Excel，导出Excel和写数据库

package workflow.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;
import org.apache.poi.ss.usermodel.CellStyle;

import pub.conn.SQLConnection;

public class ErpManager {

 /**
  * 文件上传目录
  */
 private final String uploadPath = "\\upload\\target";
 
 /**
  * 设置一旦文件大小超过getSizeThreshold()的值时数据存放在硬盘的目录
  */
 private final String tempPath = "\\temp";
 
 /**
  * 设置允许用户上传文件大小 单位：字节byte
  */
 private final int fileSizeMax = 1024 * 1024 * 100;

 /**
  * 设置最多只允许在内存中存储的数据 单位:字节
  */
 private final int sizeThreshold = 4096;
 
 /**
  * 编码
  */
 private String encoding = "utf-8";
  
 private static final Log log=LogFactory.getLog(ErpManager.class);
 
 public boolean importDate(ServletContext application,HttpServletRequest request,HttpServletResponse response)
 {
  boolean flag=true;

  String path = application.getRealPath("");
  // 临时文件夹目录
  File tempFolder = new File(path + tempPath);
  if (!tempFolder.exists())
  {
   tempFolder.mkdirs();
  }
  DiskFileItemFactory factory = new DiskFileItemFactory();
  factory.setSizeThreshold(sizeThreshold);
  factory.setRepository(tempFolder);
  ServletFileUpload sf = new ServletFileUpload(factory);
  sf.setSizeMax(fileSizeMax);
  sf.setHeaderEncoding(encoding);
  
  // 文件保存目录
  String filePath = path + uploadPath;
  File folder = new File(filePath);
  if (!folder.exists())
  {
   folder.mkdirs();
  }
  
  try {

   List fileItems = sf.parseRequest(request);
   Iterator iter = fileItems.iterator();
   while(iter.hasNext())
   {
    FileItem item = (FileItem) iter.next();
    if (!item.isFormField())
    {
     String name = item.getName();

     long size = item.getSize();
     if ((name == null || name.equals("")) && size == 0)
     {
      continue;
     }
     String fileName = name.substring(name.lastIndexOf("."));
     File file = null;
     FileInputStream fis = null;
     try
     {
      file = new File(filePath + "\\" + fileName);
      item.write(file);
      fis=new FileInputStream(file);
      readERPNumberExcelData(fis);
     }catch(Exception e){
                        log.error(this.getClass(),e);
      flag = false;
      break;
     }finally{
      /** ***关闭输入流**** */
                        fis.close();
                        if (file.exists())
      {
       file.delete();
      }
     }
    }
   }
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   flag=false;
  }
  return flag;
 }

 /**
  * 读取Excel表并存入数据存
  * @param fis
  */
 private void readERPNumberExcelData(FileInputStream fis) {
  // TODO Auto-generated method stub
  HSSFWorkbook workbook = null;
  try {
   workbook=new HSSFWorkbook(fis);
   HSSFSheet sheet=workbook.getSheetAt(0);
   //取得总行数
   int rownum = sheet.getLastRowNum();
   String workflow_id="";
   String erpnumber="";
   for(int rowIn=2;rowIn<=rownum;rowIn++)
   {
    try {
     workflow_id=getCellValue(sheet, rowIn, 1);//从Excel表中读取工单编号
     erpnumber=getCellValue(sheet,rowIn,2);//从Excel表中读取ERP项目号
     insert_erp_object_number(workflow_id,erpnumber);//添加ERP项目号到数据
    } catch (Exception e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
    
 }
 
 /**
  * 读取excel表格的值
  * @param sheet 工作簿
  * @param rowIndex 行下标,下标从1开始
  * @param cellnum 列下标,下标从1开始
  * @return
  */
 private static String getCellValue(HSSFSheet sheet,int rowIndex,int cellnum) throws Exception{
  if(sheet.getRow(rowIndex-1) == null){
   return "";
  }
  HSSFCell cell = sheet.getRow(rowIndex-1).getCell(cellnum-1);
  String result = "";
  if(cell != null){
   switch (cell.getCellType()) {
   case HSSFCell.CELL_TYPE_STRING:
    result = cell.getStringCellValue();
    break;
   case HSSFCell.CELL_TYPE_NUMERIC:
    result = String.valueOf(cell.getNumericCellValue());
    break;
   case HSSFCell.CELL_TYPE_BLANK:
    result = String.valueOf(cell.getNumericCellValue());
    break;
    
   case HSSFCell.CELL_TYPE_FORMULA:
    try{
     result = String.valueOf(cell.getNumericCellValue());
    }catch(Exception ex){
     result = "";
    }
    break;
   case HSSFCell.CELL_TYPE_BOOLEAN:
    result = String.valueOf(cell.getBooleanCellValue());
    break;
   default:
    result = "";
    break;
   }
  }
  if (result.equals("") || result == null) {  
            return "";  
        }  
        if (cell == null) {  
            return "";  
        }
  return result;
 }
  
 /**
  * 导出ERP项目号到Excel里
  */
 public void writeErpNumberExcelData(HttpServletRequest request,HttpServletResponse response)
 {

  HttpSession session=request.getSession();
  ArrayList<HashMap<String,String>> al=(ArrayList<HashMap<String,String>>)session.getAttribute("arraylist");
  
  //创建新的Excel 工作簿
  HSSFWorkbook workbook=new HSSFWorkbook();
  //在Excel 工作簿中建一工作表
  HSSFSheet sheet=workbook.createSheet("ERP项目号");
  //设置单元格格式(文本)
  HSSFCellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
  // 在索引0的位置创建行（第一行）
  HSSFRow row = sheet.createRow(0);
  HSSFCell cell1=row.createCell(0);//创建第一列
  HSSFCell cell2=row.createCell(1);//创建第二列
  sheet.setColumnWidth(1, 8000);
  // 定义单元格为字符串类型
  cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
  cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
  cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
  cell1.setCellStyle(cellStyle);
  cell2.setCellStyle(cellStyle);
  cell1.setCellValue("工单编号");
  cell2.setCellValue("ERP项目号");
  HashMap<String,String> hm=null;
 
  if(!al.isEmpty())
  {
   Iterator it=al.iterator();
   int row2=0;
   while(it.hasNext())
   {
    hm=(HashMap<String,String>)it.next();
    row2++;
    row=sheet.createRow(row2);//创建一行
    cell1=row.createCell(0);//创建第一列
    cell2=row.createCell(1);//创建第二列
    cell1.setCellValue((String)hm.get("1"));//取得工单编号
    cell2.setCellValue((String)hm.get("3"));//取得ERP项目号
   }
  }
  OutputStream os  = null;
  String fileName = "ERP项目号.xls";
  try {
   fileName = new String(fileName.getBytes("GBK"),"ISO8859_1");
  } catch (UnsupportedEncodingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  response.setContentType("application/ms-excel");
  response.setHeader("Content-Disposition", "attachment;Filename="+fileName);
  try {
   os = response.getOutputStream();
   os.flush();
   workbook.write(os);
   os.close();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   log.error(this, e);
  }  
  
 }
}