jexcelapi jxl

1.导入jxl(net.sourceforge.jexcelapi)

2.书写报表帮助类：

package com.kevin.jexcel.common;

import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JexcelHelp {
 private WritableWorkbook workbook;

 public JexcelHelp(HttpServletResponse response, String name)
   throws IOException {
  response.setContentType("application/vnd.ms-excel");
  response.setHeader("Content-Disposition", "attachment; filename="
    + name + ".xls");
  workbook = Workbook.createWorkbook(response.getOutputStream());
 }

 public WritableSheet getSheet(String name, int index) {
  WritableSheet sheet = workbook.createSheet(name, index);
  return sheet;
 }

 public void addList(WritableSheet sheet, List<String[]> results)
   throws RowsExceededException, WriteException {
  for (int i = 0; i < results.size(); i++) {
   String[] element = results.get(i);
   for (int j = 0; j < element.length; j++) {
    String text = element[j];
    if (text == null)
     text = "";
    Label label = new Label(j, i, text);
    sheet.addCell(label);
   }
  }
 }

 public void generate() throws IOException, WriteException {
  workbook.write();
  workbook.close();
 }
}

3.使用：

package com.kevin.jexcel.service;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.springframework.stereotype.Service;
import com.kevin.entity.Sysuser;
import com.kevin.jexcel.common.JexcelHelp;
import com.kevin.sysuser.service.SysuserService;

@Service
public class JexcelService {
 @Resource
 private SysuserService sysuserService;

 public void export(HttpServletResponse response) throws IOException,
   RowsExceededException, WriteException {
  // 取出数据.
  List<Sysuser> userList = sysuserService.list();
  List<String[]> userList_format = format(userList);

  // 生成报表.
  JexcelHelp jexcelHelp = new JexcelHelp(response, "userlist");
  WritableSheet firstSheet = jexcelHelp.getSheet("1", 0);
  jexcelHelp.addList(firstSheet, userList_format);
  
  // 设置报表头.
  firstSheet.insertRow(0);
  firstSheet.mergeCells(0,0,3,0);
  WritableFont font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false);
  WritableCellFormat cellFormat = new WritableCellFormat (font);
  cellFormat.setAlignment(Alignment.CENTRE);

  Label header= new Label(0, 0, "用户信息表", cellFormat);
  firstSheet.addCell(header);
  
  jexcelHelp.generate();
 }

 /**
  * 格式化数据.
  *
  * @param userList
  * @return
  */
 private List<String[]> format(List<Sysuser> userList) {
  List<String[]> userList_format = new ArrayList<String[]>();
  for (Sysuser user : userList) {
   String[] user_format = new String[4];
   user_format[0] = user.getId().toString();
   user_format[1] = user.getName();
   user_format[2] = user.getSexEnum().getLabel();
   user_format[3] = user.getState().toString();
   userList_format.add(user_format);
  }
  return userList_format;
 }
}

4.导出的excel文件如果单元格是数字的话，左上角有绿色小三角。可将excel设置成忽略拼写，将绿色小三角去掉。

 文件－》选项－》公式－》错误检查－》去掉“允许后台错误检查”的勾。