public class DownloadUtils {
/**
* 进行BASE64Encoder编码
* @param fileName
* @return
*/
public static String base64EncodeFileName(String fileName) {
BASE64Encoder base64Encoder = new BASE64Encoder();
try {
return "=?UTF-8?B?"
+ new String(base64Encoder.encode(fileName
.getBytes("UTF-8"))) + "?=";
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* @Title: exportExcel
* @Description: 文件的导出
* @param request
* @param response
* @param list 需要放入表格中的数据，格式为：List<Map<String,Object>>
* @param titleList 每一列上面的标题集合
* @param fileName 该excel的默认名称
* @param paramList 参数集合，装载上面list中Map集合的key（一 一 对 应）
* @throws ServletException
* @throws IOException: void
*/
public static void exportExcel(HttpServletRequest request, HttpServletResponse response,List<Map<String, Object>> list,
List<String> titleList ,String fileName,List<String> paramList )
throws ServletException, IOException {
//临时文件名
String path = request.getRealPath("\\excel");
String tempFileName=fileName+""+System.currentTimeMillis();
File temp=new File(path+ "/"+tempFileName+".xls");
//火狐的编码是base64 IE的编码是URL
String agent = request.getHeader("user-agent");
//需要导出的数据
//导出excel
/** **********创建工作簿************ */
try {
/** **********创建工作表************ */
WritableWorkbook workbook = Workbook.createWorkbook(temp);
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
/** **********设置纵横打印（默认为纵打）、打印纸***************** */
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);
/** **********设置页边距(0.1d=0.26cm) ***************** */
sheet.getSettings().setBottomMargin(0.7d);
sheet.getSettings().setTopMargin(0.7d);
sheet.getSettings().setLeftMargin(0.75d);
sheet.getSettings().setRightMargin(0.75d);
/** **********设置是否显示行数列数编号 ***************** */
sheet.getSettings().setPrintHeaders(true);
/** ************设置单元格字体************** */
WritableFont fontTable=new WritableFont(WritableFont.createFont("微软雅黑"),16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.WHITE);
WritableCellFormat formatTable = new WritableCellFormat(fontTable);
Color color = Color.decode("#1D933A"); // 自定义的颜色
workbook.setColourRGB(Colour.ORANGE, color.getRed(),
color.getGreen(), color.getBlue());
formatTable.setBackground(Colour.ORANGE);
WritableFont font=new WritableFont(WritableFont.createFont("微软雅黑"),12,WritableFont.NO_BOLD );
WritableCellFormat format1 = new WritableCellFormat(font);
Color colorTitle= Color.decode("#E4F7D6");
workbook.setColourRGB(Colour.AQUA, colorTitle.getRed(),
colorTitle.getGreen(), colorTitle.getBlue());
format1.setBackground(Colour.AQUA);
WritableFont font2=new WritableFont(WritableFont.createFont("微软雅黑"),12,WritableFont.NO_BOLD );
WritableCellFormat format2 = new WritableCellFormat(font2);
WritableFont font3=new WritableFont(WritableFont.createFont("微软雅黑"),12,WritableFont.BOLD );
WritableCellFormat format3 = new WritableCellFormat(font3);
// 把水平对齐方式指定为居中
formatTable.setAlignment(jxl.format.Alignment.CENTRE);
// 把垂直对齐方式指定为居中
formatTable.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
formatTable.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// 把水平对齐方式指定为居中
format1.setAlignment(jxl.format.Alignment.CENTRE);
format1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// 把垂直对齐方式指定为居中
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 把水平对齐方式指定为居中
format2.setAlignment(jxl.format.Alignment.CENTRE);
// 把垂直对齐方式指定为居中
format2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
format2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
// 把水平对齐方式指定为居中
format3.setAlignment(jxl.format.Alignment.CENTRE);
// 把垂直对齐方式指定为居中
format3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//设置边框;
format3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
//给sheet电子版中所有的列设置默认的列的宽度;
sheet.getSettings().setDefaultColumnWidth(32);
sheet.getSettings().setDefaultRowHeight(300);
//设置背景颜色;
//format1.setBackground(Colour.DARK_BLUE2);
//format2.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
/** ************以下设置三种单元格样式，灵活备用************ */
// 用于标题居中
WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_center.setWrap(true); // 文字是否换行
// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(jxl.format.Border.NONE, BorderLineStyle.MEDIUM); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setWrap(false); // 文字是否换行
wcf_left.setAlignment(Alignment.LEFT);
List<Label>labelList=new ArrayList<Label>();
//表头
// 第一个参数代表列 ，第二个参数代表行
sheet.mergeCells(0, 0, titleList.size()-1 ,0);
labelList.add(new Label(0,0, fileName,formatTable));
sheet.setRowView( 0 , 800 );
sheet.setRowView( 1 , 500 );
for (int i = 0; i < titleList.size(); i++) {
Label label= new Label(i, 1, titleList.get(i)+"",format1);
labelList.add(label);
}
for (int i = 0; i < labelList.size(); i++) {
sheet.addCell(labelList.get(i));
}
if(!list.isEmpty() && list != null){
// 第4行第2列
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map=list.get(i);
for (int j = 0; j <paramList.size(); j++) {
Label label=new Label(j, i+2, map.get(paramList.get(j))+"",format2);
sheet.addCell(label);
}
sheet.setRowView( i+2 , 450 );
}
for (int i = 0; i < paramList.size(); i++) {
sheet.setColumnView( i , 30 );
}
}
// 将格子放入工作簿中
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
// -------------------
String fileName2= null;
if(agent.contains("Firefox")){
//火狐浏览器 base64的编码处理
fileName2 = DownloadUtils.base64EncodeFileName(fileName+".xls");
}else{
//ie 或者谷歌浏览器URL编码处理
fileName2=URLEncoder.encode(fileName+".xls","utf-8");
}
InputStream input = new BufferedInputStream(new FileInputStream(path+"/"+tempFileName+".xls"));
response.reset();// 清空输出流
response.addHeader("Content-Disposition", "attachment;filename="
+ new String(fileName2.getBytes()));
response.addHeader("Content-Length", "" + new File(path+ "/"+tempFileName+".xls").length());
OutputStream out = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
int flag = 0;
while ((flag = input.read()) != -1) {
out.write(flag);
}
out.flush();
out.close();
input.close();
if (temp.exists()&&temp.isFile()) {
temp.delete();
}
}
}
浙公网安备 33010602011771号