Java 导出数据到excel

来源：互联网发布：粒子群算法应用实例编辑：程序博客网时间：2024/05/16 15:27

数据库表：

charge.java

导出数据到Excel的方法：

@RequestMapping(value = "/exportChargeInfoAsExcel")
public String exportChargeInfoAsExcel(HttpServletResponse response) throws ParseException {

Enterprises enterprisesDb = new Enterprises();

HttpServletRequest request = getRequest();
HttpSession session = request.getSession();

HashMap<String, Object> params = new HashMap<String, Object>();
enterprisesDb = (Enterprises)session.getAttribute("enterpriseUser");

List<Charge> list = null;

if(null == enterprisesDb)
{
list = chargeService.selectAll();
}
else
{
Long enterpriseId = enterprisesDb.getId();

list = chargeService.selectByEnterpriseId(enterpriseId);
}

String msg = "";
// 导出到EXCEL

response.reset();
try {
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=chargeInfo.xls"); //这里的是要导出的文件显示的名字
} catch (Exception e) {
msg = "不支持编码异常！";
}

Workbook rwb = null;
try {
WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream());

WritableSheet ws = wwb.createSheet("worksheet", 0); //添加第一个工作表

int rowNum = 1; // 行excel中从0开始第0行显示的是你的大标题所以从第一行开始显示数据
int colNum = 0; // 列
WritableFont wFont = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat wcf = new WritableCellFormat(wFont);
wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
wcf.setAlignment(jxl.format.Alignment.CENTRE);
wcf.setWrap(true);

// 企业名称
ws.addCell(new Label(colNum, 0, "企业名称", wcf));
colNum++; //放入一个数据就让列+1

//产品名称
ws.addCell(new Label(colNum, 0, "产品名称", wcf));
colNum++;

//手机号码
ws.addCell(new Label(colNum, 0, "手机号码", wcf));
colNum++;

//数量
ws.addCell(new Label(colNum, 0, "产品数量", wcf));
colNum++;

//状态
ws.addCell(new Label(colNum, 0, "产品状态", wcf));
colNum++;

//创建时间
ws.addCell(new Label(colNum, 0, "创建时间", wcf));
colNum++;

for (int iRow = 0; iRow < list.size(); iRow++) {
colNum = 0; //从第一列开始
Charge charge = list.get(iRow);//这里获取你的list

// 企业名称
String enterpriseName = charge.getEnterpriseName();
ws.addCell(new Label(colNum, rowNum, enterpriseName, wcf));
colNum++; //放入一个数据就让列+1

//产品名称
String productName = charge.getProductName();
ws.addCell(new Label(colNum, rowNum, productName, wcf));
colNum++;

//手机号码
String phone = charge.getPhone();
ws.addCell(new Label(colNum, rowNum, phone, wcf));
colNum++;

//数量
Long num = charge.getNum();
String numStr = num.toString();
ws.addCell(new Label(colNum, rowNum, numStr, wcf));
colNum++;

//状态
int status = charge.getStatus();
String statusStr = null;
if(0 == status)
{
  statusStr = "充值中";
}
else if(1 == status)
{
  statusStr = "已充值";
}
else if(2 == status)
{
  statusStr = "充值失败";
}
else if(3 == status)
{
  statusStr = "充值失败已处理";
}
else
{
  statusStr = "状态不合法";
}
ws.addCell(new Label(colNum, rowNum, statusStr, wcf));
colNum++;

//创建时间
Date date = charge.getCreateTime();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
if(date!=null && !date.equals("")){
  String dateStr = df.format(date);
ws.addCell(new Label(colNum, rowNum, dateStr, wcf));
}else{
ws.addCell(new Label(colNum, rowNum, "", wcf));
}

rowNum++;//最后的时候要使行+1 移动到下一行
}
wwb.write();
wwb.close();

return "redirect:/manage/charge/showChargeList.html";
}

catch (Exception e) {
msg = "数据没法写入excel中！！";
} finally {
rwb.close();
}
return "error";
}

效果图：

0 0