java poi 导出excel ExcelUtils

package com.zenith.common;


import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


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


import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


import com.alibaba.fastjson.JSONObject;


/**
 * excel工具类
 * @author Administrator
 *
 */
public class ExcelUtils {


/**
* 导出excel
* @param form
* @param request
* @param response
*/
public static void export(HashMap<String, String>form,LinkedList<HashMap<String, String>> dataList , HttpServletRequest request, HttpServletResponse response,Map<String,String> xlsExportColumNameMapping)
{

String fileName = form.get("fileName");
String sheetName =form.get("sheetName");
OutputStream outputStream;
try {
if(dataList==null||dataList.size()==0)
{
response.reset();
response.setContentType("text/html charset=UTF-8");
response.getWriter().write("<script>");
response.getWriter().write("alert('当前无数据可供导出!')");
response.getWriter().write("</script>");
}
else
{
String exportFileName =fileName+DateFormatUtils.format(new Date(), "yyyyMMddHHmm")+".xls";
exportFileName = setFileDownloadHeader(request,response,exportFileName);
if(exportFileName==null)
{
exportFileName = "dataList_"+DateFormatUtils.format(new Date(), "yyyyMMddHHmm"+".xls");
}
// 清空输出流
response.reset();
                //设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + exportFileName);
                // 定义输出类型
Workbook wb = new HSSFWorkbook();
// response.setContentLength(wb.toString().getBytes().length);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
outputStream = response.getOutputStream();
Object[] columnModel = xlsExportColumNameMapping.keySet().toArray();

Font headFont = wb.createFont();
// wb.getb
                // 大小
headFont.setFontHeightInPoints((short)12);
                //粗体显示
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headFont.setFontName("黑体");
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
                //设置单元格水平方向对其方式
headStyle.setAlignment(CellStyle.ALIGN_CENTER);
                //设置单元格垂直方向对其方式
headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headStyle.setBorderTop(CellStyle.BORDER_THIN);
headStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                // 左边边框
headStyle.setBorderLeft(CellStyle.BORDER_THIN);
                // 左边边框颜色
headStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                // 右边边框
headStyle.setBorderRight(CellStyle.BORDER_THIN);
                // 右边边框颜色
headStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                // 下边框
headStyle.setBorderBottom(CellStyle.BORDER_THIN);
                // 下边框颜色
headStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
headStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                // 前景色
headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                // 自动换行
headStyle.setWrapText(true);


//XLS内容样式  body style
CellStyle bodyStyle = wb.createCellStyle();


bodyStyle.setAlignment(CellStyle.ALIGN_CENTER);
bodyStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                // 左边边框
bodyStyle.setBorderLeft(CellStyle.BORDER_THIN);
                // 左边边框颜色
bodyStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                // 右边边框
bodyStyle.setBorderRight(CellStyle.BORDER_THIN);
                // 右边边框颜色
bodyStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                // 下边框
bodyStyle.setBorderBottom(CellStyle.BORDER_THIN);
                // 下边框颜色
bodyStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                // 自动换行
bodyStyle.setWrapText(true);
//创建sheet
Sheet sheet = wb.createSheet(sheetName);
//生成XLS sheet头 头标题
Row sheetHeadRow = sheet.createRow(0);
sheetHeadRow.setHeight((short)400);
for (int i = 0; i < columnModel.length; i++) {
Cell cell = sheetHeadRow.createCell(i);
cell.setCellValue(columnModel[i].toString());
cell.setCellStyle(headStyle);
//设置列宽
if(i==4){
sheet.setColumnWidth(i, 10000);
}else if(i==7){
sheet.setColumnWidth(i, 8000);
}else{
sheet.setColumnWidth(i, 5000);
}
}
//生成XLS sheet body 数据行
for(int i = 0; i < dataList.size(); i++) {
                    // sheet页数据行
Row sheetRow = sheet.createRow(i+1);
                    // 数据库数据行
HashMap<String, String> datatrow = dataList.get(i);
for (int j = 0; j < columnModel.length; j++) {
Cell cell = sheetRow.createCell(j);
String tempKey = columnModel[j].toString();
Object fieledname = xlsExportColumNameMapping.get(tempKey);
if(null!=fieledname){
Object dataValue = datatrow.get(fieledname.toString());
if (null!=dataValue) {
cell.setCellValue(dataValue.toString());
cell.setCellStyle(bodyStyle);
}else{
////没有内容时也能生成XLS的边框格子美观些
cell.setCellValue("");
cell.setCellStyle(bodyStyle);
}
}
}
}
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}
}catch (Exception e) {
e.printStackTrace();
}
}

   /**
    * 导出Excel中文文件名称处理
    * @param req
    * @param resp
    * @param fileName
    * @return
    */
private static String setFileDownloadHeader(HttpServletRequest req,HttpServletResponse resp,String fileName){
final String userAgent = req.getHeader("USER-AGENT");
String finalFileName =null;
try {
if(StringUtils.contains(userAgent, "MSIE")){
               //IE浏览器
finalFileName = URLEncoder.encode(fileName,"UTF8");
}else if(StringUtils.contains(userAgent, "Mozilla")){
               //google 火狐浏览器
finalFileName = new String(fileName.getBytes(),"ISO8859-1");
}else{
               //其他浏览器
finalFileName = URLEncoder.encode(fileName,"UTF8");
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return finalFileName;
}

/**
* 
* @param fileName 文件名字
* @param is excel文件输入流
* @param pzData 配置数据
* @throws IOException 
* @throws InvalidFormatException 
*/
public static Map<String, Object> intportExcel(String fileName,InputStream is,String pzData) throws IOException, InvalidFormatException
{
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("resultCode", true);
boolean isExcel2003 = isExcel2003(fileName);
 Workbook wb = null;
if(isExcel2003)
{
//excel是2003 
wb = new HSSFWorkbook(is);
}  
else
{
   //WorkbookFactory
               //当excel是2007及以上版本
               wb = WorkbookFactory.create(is);
    }
List<String>batchSql = new ArrayList<String>();
Map<String, Object> resultMap2 =readExcelValue(pzData,wb);
if((Boolean)resultMap2.get("resultCode") == true)
{
List<List<Map<String,Object>>> list = (List<List<Map<String, Object>>>) resultMap2.get("resultData");
if(list.size()>0)
{
String taleName = (String) resultMap2.get("tableName");
 for (List<Map<String, Object>> list2 : list)
 {
 //一条数据
 StringBuffer sqlBuffer = new StringBuffer();
 sqlBuffer.append("insert into "+taleName+"( ");
 String keySql = "";
 String valueSql = "";
for (Map<String, Object> map : list2) 
{
String columnType = (String) map.get("columnType");
String value = (String) map.get("value");
String columnName = (String) map.get("columnName");
keySql +=columnName+",";
if("date".equals(columnType))
{//时间格式数据
valueSql+="to_date('"+value+"','yyyy-MM-dd HH24:mi:ss')"+",";
}
else 
{
value = "'"+value+"'";
valueSql += value+",";
//其他格式待开发
}
}
keySql = keySql.substring(0, keySql.length()-1);
valueSql = valueSql.substring(0, valueSql.length()-1);
sqlBuffer.append(keySql+")values("+valueSql+")");
batchSql.add(sqlBuffer.toString());
}
}  
resultMap.put("resultData", batchSql);
}
else
{
String msg = (String) resultMap2.get("resultMsg");
resultMap.put("resultCode", false);
resultMap.put("resultMsg",msg);
}
return  resultMap;
}

/**
* 读取excel里的内容生成list
* @param pzData
* @param wb
* @return
*/
private static Map<String, Object>readExcelValue(String pzData,Workbook wb)
{
Map<String,Object> pzDataMap = dealParamsToMap(pzData);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("resultCode", true);
List<List<Map<String,Object>>> list = new ArrayList<List<Map<String,Object>>>();
String tableName = (String) pzDataMap.get("tableName");
List<Map<String, Object>> pzList =  (List<Map<String, Object>>) pzDataMap.get("PzData");
//得到第一个shell
       Sheet sheet=wb.getSheetAt(0);
       //行数
       int hs = sheet.getPhysicalNumberOfRows();
       //列数
       int ls = 0;
       if(hs >= 1 && sheet.getRow(0) != null )
       {
        ls = pzList.size();
       }
       //循环Excel行数,从第二行开始。标题不入库
       for(int r=1;r<hs;r++)
       {
           Row row = sheet.getRow(r);
           if (row == null)
           {
               continue;
           }
           List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
           try {
               Thread.sleep(1);
           }catch (InterruptedException e)
           {
               e.printStackTrace();
           }
           //循环Excel的列
           for(int c = 0; c <ls; c++)
           {
               Map<String, Object> finalmap = new HashMap<String, Object>();
            Map<String, Object> pzMap = pzList.get(c);//当前cell的配置
            String columnName = (String) pzMap.get("columnName");//数据库字段名
            String length  = (String) pzMap.get("length");//数据库字段长度
            String isNull = (String) pzMap.get("isNull");//是否可为空标志 true 可以 false 不可以
            String columnType = (String) pzMap.get("columnType");
            String isTranslate = (String) pzMap.get("isTranslate");
   //是否需要翻译  true 是 false 否   //下拉就用翻译
            Cell cell = row.getCell(c); //得到第几行第几列的cell
               if (null != cell){
            String value = cell.getStringCellValue().trim();
            if("false".equals(isNull) && "".equals(value))
            {
            resultMap.put("resultCode", false);
            resultMap.put("resultMsg", "第"+hs+"第"+ls+"不能为空");
            return resultMap;
            }  
            if(value.length()>Integer.parseInt(length)/4)
            {
            resultMap.put("resultCode", false);
            resultMap.put("resultMsg", "第"+hs+"第"+ls+"列数据太长");
            return resultMap;
            }  
            if("true".equals(isTranslate))
            {
            //是否需要翻译 待开发
            }  
            finalmap.put("columnName", columnName);
            finalmap.put("columnType", columnType);
            finalmap.put("value", value);
           }
               mapList.add(finalmap);
          }
           list.add(mapList);
      }
       resultMap.put("resultData", list);
       resultMap.put("tableName", tableName);
return  resultMap;

}

   /**
    * 描述：是否是2003的excel，返回true是2003
    * @param filePath
    * @return
    */
   public static boolean isExcel2003(String filePath)
   {
       return filePath.matches("^.+\\.(?i)(xls)$");
   }


   /**
    * excel版本是2007以上
    * @param filePath
    * @return
    */
   public static boolean isExcel2007(String filePath)
   {
       return filePath.matches("^.+\\.(?i)(xlsx)$");
   }
   
/**
* 
* <p>说明：将json字符串处理为Map对象</p>
* <p>时间：2016-3-10 下午4:14:02</p>
* @param params
* @return
*/
protected static Map<String,Object> dealParamsToMap(String params)
{
Map<String,Object> map = new HashMap<String,Object>();
if(StringUtils.isEmpty(params)){
return map;
}
try {
map =  JSONObject.parseObject(params);
} catch (Exception e) {
e.printStackTrace();
return map;
}
return map;
}
}