1.首先，先导apache.poi的jar包poi-excelant-3.15-beta1.jar
2.创建导出工具类ExportExcelUtil。
public class ExportExcelUtil {
/**
*
* @param fileName 文件名
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("deprecation")
public static HSSFWorkbook exportExcel(HttpServletRequest request,HttpServletResponse response,String fileName, String[] headers, List<Object[]> dataset, String pattern) {
String docsPath = request.getSession().getServletContext().getRealPath("docs/");//docs文件夹目录
String filePath =docsPath + fileName;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("Excel");
// 设置表格默认列宽度为15个字节
//sheet.setDefaultColumnWidth((short) 15);
// 生成一个表格标题行样式
HSSFCellStyle style = getColumnTopStyle(workbook);
// 生成非标题样式
HSSFCellStyle style2 = getColumnStyle(workbook);
// 产生表格标题行
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell0 = row0.createCell(0);
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 8);
sheet.addMergedRegion(cra);//合并单元格并居中
cell0.setCellStyle(style);
HSSFRichTextString text0 = new HSSFRichTextString("罗湖区小区业委会及物业项目统计报表（单位：个数/百分比%）");
cell0.setCellValue(text0);
HSSFRow row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<Object[]> it = dataset.iterator();
int index = 1;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);//从第1行开始创建
Object[] obj = (Object[]) it.next();
for (short i = 0; i < obj.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Object value = obj[i];
String textValue = null;
if (!"".equals(value) && value != null) {
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Date) {
Date date = (Date) value;
if(null==pattern||pattern.equals("")){
pattern="yyyy-MM-dd";
}
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
cell.setCellValue(textValue);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
cell.setCellValue(textValue); // 设置单元格的值
}
} else {
cell.setCellValue("");
}
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < headers.length; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue() != null
? currentCell.getStringCellValue().getBytes().length : 10;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
try {
if (createDir(docsPath)) {
response.reset();
OutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.flush();
out.close();
download(filePath, response,request);// 执行下载
//cleanFile(filePath);// 删除已完成下载的文件
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
/**
* 导出下载,弹出下载框
* @param path 文件下载路径
* @param response
*/
public static void download(String path, HttpServletResponse response,HttpServletRequest request) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType(request.getServletContext().getMimeType(URLEncoder.encode(filename, "UTF-8")));
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* 清除文件
* @param filePath 文件路径
*/
public static void cleanFile(String docsPath) {
File file = new File(docsPath);
file.delete();
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体颜色
font.setColor(HSSFColor.VIOLET.index);
// 设置字体大小
font.setFontHeightInPoints((short) 12);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 在样式用应用设置的字体;
style.setFont(font);
return style;
}
/*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook) {
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 设置字体名字
font.setFontName("Courier New");
// 在样式用应用设置的字体;
style.setFont(font);
return style;
}
/**
* 创建目录
* @param destDirName
* @return true 存在目录 false 不存在目录
*/
public static boolean createDir(String destDirName) {
File dir = new File(destDirName);
if (dir.exists()) {
return true;
}
if (!destDirName.endsWith(File.separator)) {
destDirName = destDirName + File.separator;
}
// 创建目录
if (dir.mkdirs()) {
System.out.println("创建目录" + destDirName + "成功！");
return true;
} else {
System.out.println("创建目录" + destDirName + "失败！");
return false;
}
}
}3.控制层调用
public void excelXiaoqu(HttpServletResponse response,HttpServletRequest request,String sid,String cid){
Map<String, Object> map = new HashMap<String, Object>();
List<String> sList = new ArrayList<String>();
List<String> cList = new ArrayList<String>();
if(sid!=null && sid!=""){
if (sid.indexOf(",") != -1) {
String[] gid = sid.split(",");
for (int i = 0; i < gid.length; i++) {
sList.add(gid[i]);
}
} else {
sList.add(sid);
}
}
if(cid!=null && cid!=""){
if (cid.indexOf(",") != -1) {
String[] gid = cid.split(",");
for (int i = 0; i < gid.length; i++) {
cList.add(gid[i]);
}
} else {
cList.add(cid);
}
}
map.put("sList", sList);
map.put("cList", cList);
List<District> selBumenSat = districtService.statisticalArea(map);
String[] title = {"序号","名称"};//标题
//excel文件名
String fileName = "文件名"+".xls";
List<Object[]> dataList = new ArrayList<Object[]>();
int zdNum=0;
int cAmount=0;
int pAmount=0;
int zAmount=0;
NumberFormat numberFormat = NumberFormat.getInstance();
numberFormat.setMaximumFractionDigits(2);
for (int i = 0; i < selBumenSat.size(); i++) {
Object[] objs = new Object[title.length];// 创建9个数组长度
District sto = selBumenSat.get(i);
int dNum=Integer.parseInt(sto.getdNum());
int cNum=Integer.parseInt(sto.getcNum());
int pNum=Integer.parseInt(sto.getpNum());
int zNum=Integer.parseInt(sto.getzNum());
int ncNum=dNum-cNum;
int npNum=dNum-pNum;
int byNum=cNum-zNum;
int wyNum=pNum-zNum;
int nzNum=dNum-zNum-byNum-wyNum;
String result = numberFormat.format((float) cNum / (float) dNum * 100)+"%";
String result1 = numberFormat.format((float) ncNum / (float) dNum * 100)+"%";
String code = numberFormat.format((float) pNum / (float) dNum * 100)+"%";
String code1 = numberFormat.format((float) npNum / (float) dNum * 100)+"%";
String zPercent = numberFormat.format((float) zNum / (float) dNum * 100)+"%";
String nzPercent = numberFormat.format((float) nzNum / (float) dNum * 100)+"%";
String byPercent = numberFormat.format((float) byNum / (float) dNum * 100)+"%";
String wyPercent = numberFormat.format((float) wyNum / (float) dNum * 100)+"%";
objs[0] = sto.getsName();
objs[1] = cNum+"/"+result;
objs[2] = ncNum+"/"+result1;
objs[3] = pNum+"/"+code;
objs[4] = npNum+"/"+code1;
objs[5] = zNum+"/"+zPercent;
objs[6] = nzNum+"/"+nzPercent;
objs[7] = byNum+"/"+byPercent;
objs[8] = wyNum+"/"+wyPercent;
dataList.add(objs);
zdNum+=Integer.parseInt(sto.getdNum());
cAmount+=Integer.parseInt(sto.getcNum());
pAmount+=Integer.parseInt(sto.getpNum());
zAmount+=Integer.parseInt(sto.getzNum());
}
Object[] obj = new Object[title.length];// 创建9个数组长度
int ncAmount=zdNum-cAmount;
int npAmount=zdNum-pAmount;
int byAmount=cAmount-zAmount;
int wyAmount=pAmount-zAmount;
int nzAmount=zdNum-zAmount-byAmount-wyAmount;
String cSign = numberFormat.format((float) cAmount / (float) zdNum * 100)+"%";
String ncSign = numberFormat.format((float) ncAmount / (float) zdNum * 100)+"%";
String pSign = numberFormat.format((float) pAmount / (float) zdNum * 100)+"%";
String npSign = numberFormat.format((float) npAmount / (float) zdNum * 100)+"%";
String zSign = numberFormat.format((float) zAmount / (float) zdNum * 100)+"%";
String nzSign = numberFormat.format((float) nzAmount / (float) zdNum * 100)+"%";
String bySign = numberFormat.format((float) byAmount / (float) zdNum * 100)+"%";
String wySign = numberFormat.format((float) wyAmount / (float) zdNum * 100)+"%";
obj[0] = "总计";
obj[1] = cAmount+"/"+cSign;
obj[2] = ncAmount+"/"+ncSign;
obj[3] = pAmount+"/"+pSign;
obj[4] = npAmount+"/"+npSign;
obj[5] = zAmount+"/"+zSign;
obj[6] = nzAmount+"/"+nzSign;
obj[7] = byAmount+"/"+bySign;
obj[8] = wyAmount+"/"+wySign;
dataList.add(obj);
ExportExcelUtil.exportExcel(request,response,fileName, title, dataList, "yyyy-MM-dd HH:mm:ss");
}到这里导出功能已搞定。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
