poi-3.8-20100326.jar
1。
package com.jnpc.protect.radiation.action;
import java.io.FileOutputStream;
import java.io.IOException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
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;
import org.apache.poi.ss.usermodel.Cell;
/**
* 生成excel
* 使用的是HSSFWorkbook,仅能生成xls格式的excel
* 在创建存储excel文件流的时候注意不能用.xlsx的扩展名
* @author Uself 2017-10-12 下午4:21:43
* @see http://www.cnblogs.com/yxth/p/6495244.html
* http://blog.csdn.net/preterhuman_peak/article/details/40615303
*/
public class CreateExcel {
//excel表头名称
private static String[] COLUMN_NAMES = {"ID", "姓名", "年龄", "性别"};
//填充数据的key
private static String[] COLUMNS = {"id", "name", "age", "sex"};
public static void main(String[] args) {
JSONArray ary = new JSONArray();
JSONObject obj = new JSONObject();
JSONObject obj2 = new JSONObject();
JSONObject obj3 = new JSONObject();
obj.put("id", "1");
obj.put("name", "小明");
obj.put("age", "18");
obj.put("sex", "男");
obj2.put("id", "2");
obj2.put("name", "小华");
obj2.put("age", "20");
obj2.put("sex", "男");
obj3.put("id", "3");
obj3.put("name", "小丽");
obj3.put("age", "18");
obj3.put("sex", "女");
ary.add(obj);
ary.add(obj2);
ary.add(obj3);
createExcel(ary, "第一页", "D:/testCreateExcel.xls");
}
/**
* 生成excel表格
* @param jsonArray 填充excel的对象,可以灵活的修改成List<T>
* @param sheetName sheet页的名字
* @param savePath excel保存的路径 D:/text.xls
*/
public static void createExcel(JSONArray jsonArray, String sheetName, String savePath){
FileOutputStream out = null;
try {
//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = createSheet(workbook, sheetName);
//创建单元格样式
HSSFCellStyle style = createCellStyle(workbook);
//创建头部
createHead(sheet, style);
//创建主体内容
createMainContent(sheet, style, jsonArray);
out = new FileOutputStream(savePath);
workbook.write(out);
/*直接下载
response.setContentType("multipart/form-data");
String fileName = "会议执行情况汇总表(" + month + "月).xls";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ";filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
workbook.write(bos);
bos.flush();
workbook.close();*/
} catch (Exception e) {
e.printStackTrace();
System.out.println("======e========" + e.getMessage());
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 创建sheet
* @param workbook 工作簿
* @param sheetName sheet名字
* @return
*/
public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName){
HSSFSheet sheet = null;
//2创建sheet表格
sheet = workbook.createSheet(sheetName);
//设置所有的单元格格式
sheet.setDefaultColumnWidth(20);
//单独设置时必须是256的倍数
//sheet.setColumnWidth(0,5120);
//sheet.setColumnWidth(1,5120);
//sheet.setColumnWidth(2,25600);
return sheet;
}
/**
* 创建单元格样式
* @param workbook 工作簿
* @return
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook){
HSSFCellStyle style = null;
//1创建文字样式
HSSFFont font = workbook.createFont();
//设置文字字体
font.setFontName("宋体");
//设置文字大小
font.setFontHeightInPoints((short) 10);
//2.创建单元格样式
style = workbook.createCellStyle();
// 左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 上下居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置边框大小
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//3.将文字样式应用到单元格中
style.setFont(font);
return style;
}
/**
* 设置excel头部
* @param sheet
* @param style
*/
public static void createHead(HSSFSheet sheet, HSSFCellStyle style){
HSSFRow row = sheet.createRow(0);
for(int i = 0; i < COLUMN_NAMES.length; i ++){
Cell cell = row.createCell(i);
cell.setCellValue(COLUMN_NAMES[i]);
cell.setCellStyle(style);
}
}
/**
* 创建主题内容
* @param sheet sheet表格
* @param style 单元格样式
* @param jsonArray json数据对象
*/
public static void createMainContent(HSSFSheet sheet, HSSFCellStyle style, JSONArray jsonArray){
/*
* 遍历数据创建相应的行以及单元格
* 第一行已经创建,从第二行开始创建,下标为1
*/
for(int i = 1; i <= jsonArray.size(); i ++){
//创建行
HSSFRow row = sheet.createRow(i);
JSONObject jsonObject = jsonArray.getJSONObject(i - 1);
for(int j = 0; j < COLUMNS.length; j ++){
Cell cell = row.createCell(j);
cell.setCellValue(jsonObject.getString(COLUMNS[j]));
cell.setCellStyle(style);
}
}
}
}
在文件输出的时候,添加下面的代码可以定义名称,实现直接下载,不需要指定文件目录
OutputStream out = response.getOutputStream();
String fileName = "excel.xls";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ";filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
workBook.write(out);