Java POI 导入Excell

##Java POI 导入Excell
导入表格
public String toExcel(HttpServletRequest request, HttpServletResponse response,String phone,String time,String sendListid,Integer type) throws IOException {
Map<String,Object> map=new HashMap<>();
if(time!=null && !"".equals(time)){
String time1=time.substring(0,10);
String time2=time.substring(13,23);
map.put(“time1”,time1);
map.put(“time2”,time2);
}
map.put(“phone”,phone);
map.put(“sendListid”,sendListid);
map.put(“type”,type);
List<Map<String,Object>> frequencyList = frequenceService.findAll(map);
HSSFWorkbook wb = null;

    //List<MenuTree> ZorganizeFunctionsList=organizationalFormatService.getOrganizeFunctions(organizationalFormatId);
    //创建第一行表头
    String title="抽奖列表";
    //创建第二行表头
    String[] title2={"序号","手机号","微信名","openId","金额","订单编号","时间"};
    //创建Excel工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    //创建一个工作表sheet
    XSSFSheet sheet = workbook.createSheet("sheet0");
    //设置单元格宽度
    sheet.setColumnWidth(1,25*256);
    sheet.setColumnWidth(2,25*256);
    sheet.setColumnWidth(3,25*256);
    sheet.setColumnWidth(4,20*256);
    sheet.setColumnWidth(5,30*256);
    sheet.setColumnWidth(6,30*256);

    //创建第一行
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell=row.createCell(0);
    row.setHeight((short) 400);
    // 定义单元格为字符串类型
    cell.setCellType(HSSFCell.ENCODING_UTF_16);
    cell.setCellValue(title);
    //合并范围,参数(起始行号<int firstRow>，终止行号<int lastRow>， 起始列号<int firstCol>，终止列号<int lastCol>).(从0开始算,0代表Excel的第一行/列)
    CellRangeAddress CellRange=new CellRangeAddress(0,0,0,4);
    sheet.addMergedRegion(CellRange);
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    //指定单元格居中对齐
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //指定单元格垂直居中对齐
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    //设置单元格字体
    XSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontName("宋体");
    font.setFontHeight((short) 400);
    cellStyle.setFont(font);

    //指定单元格为cell
    cell.setCellStyle(cellStyle);


    //创建第1行
    XSSFRow row2=sheet.createRow(1);
    XSSFCell cell2x=null;

    //插入第二行数据
    for (int i = 0; i < title2.length; i++) {
        cell2x= row2.createCell(i);
        cell2x.setCellValue(title2[i]);
        cell2x.setCellStyle(cellStyle);
    }


    int flay=2;
    for(Map<String,Object> frequency:frequencyList){

        XSSFRow row3=sheet.createRow(flay);
        //排行
        XSSFCell GCell=row3.createCell(0);
        GCell.setCellValue(flay-1);
        //手机
        XSSFCell ACell=row3.createCell(1);
        ACell.setCellValue(frequency.get("phone").toString());
        //微信名称
        XSSFCell BCell=row3.createCell(2);
        BCell.setCellValue(frequency.get("weName").toString());
        //openId
        XSSFCell CCell=row3.createCell(3);
        CCell.setCellValue(frequency.get("openId").toString());
        //金额
        XSSFCell DCell=row3.createCell(4);
        DCell.setCellValue(frequency.get("moneys").toString());
        //订单编号
        XSSFCell ECell=row3.createCell(5);
        if (frequency.get("sendListid")!=null){
            ECell.setCellValue(frequency.get("sendListid").toString());
        }else {
            ECell.setCellValue("");
        }

        //时间
        XSSFCell FCell=row3.createCell(6);
        FCell.setCellValue(StringUtil.dateToString1((Date) frequency.get("createTime")));

        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        //指定单元格居中对齐
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //指定单元格垂直居中对齐
        cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //指定单元格为ACell
        FCell.setCellStyle(cellStyle2);
        ACell.setCellStyle(cellStyle2);
        BCell.setCellStyle(cellStyle2);
        CCell.setCellStyle(cellStyle2);
        DCell.setCellStyle(cellStyle2);
        ECell.setCellStyle(cellStyle2);
        FCell.setCellStyle(cellStyle2);
        GCell.setCellStyle(cellStyle2);
        flay+=1;

    }
    //String pathUrl=request.getSession().getServletContext().getRealPath("");//存放的路径
    String path1 = ResourceBundleUtil.getString("system", "fileupload.path");
   // File oldFile = new File(pathUrl).getParentFile();
    String fileName2="抽奖列表";
    //获取当前系统路径分隔符
    //String s=File.separator;
    //String pathUrl2=pathUrl.substring(0,pathUrl.lastIndexOf(s));

    String message="file/"+ fileName2 + ".xls";

    File file=new File(path1,message);

    try {
        file.createNewFile();
        //打开文件输出流
        FileOutputStream fos = FileUtils.openOutputStream(file);
        //将workbook写入流

        workbook.write(fos);
        fos.flush();
        fos.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

    // String fileName=pathUrl2+s+"file"+s+fileName2+".xls";
    String fileName=path1+"/"+message;
    String downloadFilename=fileName2+".xls";

    FileDownLoadUtil.downLoad(response, fileName, downloadFilename);


    return null;

}

2.工具类

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;

/**
*

• 该工具类实现文件的下载，文档、图片均可
  @
  /
  public class FileDownLoadUtil {
  /

  • @param response

  • @param fileName 完整文件路径

  • @param downloadFilename 在下载框默认显示的文件名–中文是没有问题的,带后缀名

  • 注意：文件名拼接的时候注意，不同的系统的斜杠不同，使用File.separator自动识别

  • @throws IOException
    */
    public static void downLoad(HttpServletResponse response,String fileName,String downloadFilename) throws IOException{

    // 对默认下载的文件名编码。不编码的结果就是，在客户端下载时文件名乱码
    downloadFilename = URLEncoder.encode(downloadFilename, “UTF-8”);

    File file = new File(fileName);
    if (file.exists()) {
    // 写明要下载的文件的大小
    response.setContentLength((int) file.length());
    // 设置在下载框默认显示的文件名
    response.setHeader(“Content-Disposition”, “attachment;filename=”
    + downloadFilename);
    // 指明response的返回对象是文件流
    response.setContentType(“application/octet-stream”);
    // 读出文件到response
    // 这里是先需要把要把文件内容先读到缓冲区
    // 再把缓冲区的内容写到response的输出流供用户下载
    FileInputStream fileInputStream = new FileInputStream(file);
    BufferedInputStream bufferedInputStream = new BufferedInputStream(
    fileInputStream);
    byte[] b = new byte[bufferedInputStream.available()];
    bufferedInputStream.read(b);
    OutputStream outputStream = response.getOutputStream();
    outputStream.write(b);
    // 人走带门
    bufferedInputStream.close();
    outputStream.flush();
    outputStream.close();
    }

  }

}