最近一直在做项目，现在总算告一段落了，对于项目中的一些技术还是要进行总结一下。项目中常常会用到导出功能这篇博客分析一下如何用java代码导出excel？
目录
-
html
-
js
=====================================================================================
-
java代码
controller层
service层
@Override
public void exportXscjDetail(XscjModel model, HttpServletResponse response) throws IOException {
HashMap<String, Object> param=new HashMap<String, Object>();
if(StringUtils.isNotBlank(model.getXq())) {
param.put("xq", model.getXq());
}
if(StringUtils.isNotBlank(model.getSsxn())) {
param.put("xn", model.getSsxn());
}
/*String title=xn+"年"+"第"+xq+"学期学生成绩汇总";*/
//导出表头
List<String> hear = ProUtil.getTableList("list_xscj_detial");
List<String> lbmcList = new ArrayList<>();
lbmcList.add("xh");
lbmcList.add("xm");
lbmcList.add("bjdm");
lbmcList.add("bjmc");
lbmcList.add("kcdm");
lbmcList.add("kczwmc");
lbmcList.add("ssxn");
lbmcList.add("xq");
lbmcList.add("llcj");
List<LinkedHashMap<String, String>> dataList=mapper.getXscjList(param);
if( dataList!=null && dataList.size()>=0 ){
String[] headerList = hear.toArray(new String[hear.size()]);
String[] columnArr = lbmcList.toArray(new String[lbmcList.size()]);
Vector<String[]> v = new Vector<String[]>();
v.add(headerList);
if( dataList.size()>0 ){
for (LinkedHashMap<String, String> data : dataList) {
String[] val = new String[columnArr.length];
for(int j=0; j<columnArr.length; j++ ){
String column = columnArr[j].toUpperCase();
Object columnName = data.get(column);
if(columnName != null){
val[j] = columnName.toString();
}else{
val[j] = Constants.STRING_NULL;
}
}
v.add(val);
}
}
String xlsname = "学生成绩详细数据";
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition","attachment; filename="+URLEncoder.encode(xlsname,"utf-8")+".xls");
response.setContentType("application/vnd.ms-excel");
ExportExcel.expData(v, xlsname, response.getOutputStream());
}
}
dao层
entity类
- public class XscjModel extends BaseModel{
- private String id;
- private String xh;
- private String bjdm;
- private String kcdm;
- private String llcj;
- private String czsj;
- private String sfdr;
- private String sftj;
- private String tjr;
- private String xkkh;
- private String bjmc;
- private String kcmc;
- private String xm;
- private String ssxn;
- private String xq;
- private String searchValue;
- private String kczwmc;
sql语句
类
=======================================poi工具类================================================
package com.zfsoft.zhxq.common.constants;
import java.io.OutputStream;
import java.util.Vector;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExportExcel {
//每个sheet最大记录数
private static final int SheetTotal = 40000;
@SuppressWarnings("rawtypes")
public static void expData(Vector v, String xlsname, OutputStream os) {
WritableWorkbook wwb;
if(xlsname == null || xlsname.length() == 0){
xlsname = String.valueOf(System.currentTimeMillis());
}
try {
/** ************设置单元格字体************** */
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
/** ************以下设置三种单元格样式，灵活备用************ */
// 用于标题居中
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(false); // 文字是否换行
// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行
wwb = Workbook.createWorkbook(os);
double z = Math.ceil( (double) v.size() / SheetTotal );
for (int a = 0; a < z; a++) {
String xlsName = xlsname;
if(z > 1) xlsName = xlsname + a; //有多个sheet时 页签名字后面加序号
WritableSheet ws = wwb.createSheet(xlsName, a);
ws.setName(xlsName);
/**************标题*************/
String[] tmpTile = (String[]) v.toArray()[0];
for (int j = 0; j < tmpTile.length; j++) {
ws.addCell(new Label(j, 0 , tmpTile[j],wcf_center));
}
for (int i = 0; i < SheetTotal; i++) {
int numNow = i + a * SheetTotal + 1;
if(numNow < v.size()){
String[] tmp = (String[]) v.toArray()[numNow];
for (int j = 0; j < tmp.length; j++) {
ws.addCell(new Label(j, i+1 , tmp[j],wcf_left));
}
}
}
}
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
==============================================表头工具类=========================================
package com.zfsoft.zhxq.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ProUtil {
private static Map<String, List<String>> tableMap = null;
private static Map<String, String> parmMap = null;
public static List<String> getTableList(String type) {
if(tableMap == null){
tableMap = new HashMap<String, List<String>>();
List<String> list_xscj_detial = new ArrayList<String>();
list_xscj_detial.clear();
list_xscj_detial.add("学号");
list_xscj_detial.add("姓名");
list_xscj_detial.add("班级代码");
list_xscj_detial.add("班级名称");
list_xscj_detial.add("课程代码");
list_xscj_detial.add("课程名称");
list_xscj_detial.add("学年");
list_xscj_detial.add("学期");
list_xscj_detial.add("理论成绩");
tableMap.put("list_xscj_detial", list_xscj_detial);
}
return tableMap.get(type);
}
public static String getParmMap(String type) {
if(parmMap == null){
parmMap = new HashMap<String, String>();
parmMap.put("非学缘", "0");
parmMap.put("学缘", "1");
}
return parmMap.get(type);
}
}
=============================================思考的地方===========================================
1如何.使用工具类？
2.如何确保导出的列和需求的列 一致性问题？
59
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
