Excel导入导出

1.ExcelUtil

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;


import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExcelUtil {

  //创建表头
    public static void createTitle(HSSFWorkbook workbook,HSSFSheet sheet,String str){
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(3,17*256);
        
        String [] strArray = str.split(",");
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);
        HSSFCell cell;
        for (int i =0; i<strArray.length;i++){
        cell = row.createCell(i);
        cell.setCellValue(strArray[i]);
        cell.setCellStyle(style);
        }
       
    }




    //生成excel文件
    public static void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }


    //浏览器下载excel
    public static void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }




}

 

 

2.Controller

/*

* 导出出库
*/
@RequestMapping("/printChuKu")
@ResponseBody
public String printChuKU(HttpServletResponse response) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("库存操作记录表");
createKWTitle(workbook,sheet);
// 设置日期格式
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));


List<Operation> rows = operationService.findByType(10);
// 新增数据行，并且设置单元格数据
int rowNum = 1;
for (Operation kw : rows) {
HSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(rowNum);
row.createCell(1).setCellValue(kw.getUser_name());
row.createCell(2).setCellValue(kw.getFunction());
row.createCell(3).setCellValue(kw.getContent());
String date = DateUtil.DatetoString(kw.getOptionDate(), "yyyy-MM-dd");
row.createCell(4).setCellValue(date);
rowNum++;
}


String fileName = "导出部门出库记录excel表格.xls";


// 生成excel文件
buildExcelFile(fileName, workbook);


// 浏览器下载excel
buildExcelDocument(fileName, workbook, response);


return "download excel";

}



// 生成excel文件
protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
FileOutputStream fos = new FileOutputStream(filename);
workbook.write(fos);
fos.flush();
fos.close();
}


// 浏览器下载excel
protected void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response)
throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();

}



private void createKWTitle(HSSFWorkbook workbook,HSSFSheet sheet){
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(3,17*256);


        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);


        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);
        
        cell = row.createCell(1);
        cell.setCellValue("操作人员");
        cell.setCellStyle(style);
        
        cell = row.createCell(2);
        cell.setCellValue("操作功能");
        cell.setCellStyle(style);
        
        cell = row.createCell(3);
        cell.setCellValue("操作内容");
        cell.setCellStyle(style);
        
        cell = row.createCell(4);
        cell.setCellValue("操作时间");
        cell.setCellStyle(style);


    }