Question

struts2 从数据库 导出Excel

我用Struts2上传Excel文件里的内容到数据库存起来， 已经做完了，

现在遇到的问题是，
我先描述一下我的项目相关Excel导出的问题
我这个项目是一个SSH集成的；
我这个是一个员工详细信息；字段，有 ID，名字，部门，联系方式，家庭住址一些字段，
我是从前台一个一个员工详细信息页面，然后通过checkbox选项把选择的员工的详细信息导出来。也就是通过把ID传到Action，然后掉用Service层，然后Service层把ID遍历出来，通过ID调用dao层把每个ID的详细信息查出来，然后要整到Excel里去我就不知道怎么办了

Answer 1

public class ExcelHelperPro {
private static final Log log = LogFactory.getLog(ExcelHelper.class);

public static final int FMT_TITLE = 1;
public static final int FMT_KEY = 2;
public static final int FMT_DATA = 3;

private OutputStream outStream;
private HttpServletResponse response;
private String font;
private int fontSize;

private int sheetCounter;//工作表计数器

private WritableWorkbook book = null;

private List partsList;
private List partsTitle;
private List linesList;

WritableCellFormat titleFormat;
WritableCellFormat keyFormat;
WritableCellFormat dataFormat;

public void setFont(String font) {
this.font = font;
}

public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}

private void setResponse(HttpServletResponse response) {
this.response = response;
this.response.setContentType("application/vnd.ms-excel");
try {
this.outStream = response.getOutputStream();
} catch (IOException e) {
log.debug("get outputStream from response error: " + e);
}
}

public ExcelHelperPro(HttpServletResponse response, String fileName) throws Exception {
setResponse(response);//设置输出流

font = "宋体";
fontSize = 9;
sheetCounter = 0;
partsList = new ArrayList();
partsTitle = new ArrayList();
linesList = new ArrayList();

titleFormat = new WritableCellFormat(new WritableFont(WritableFont.createFont(font), fontSize,
WritableFont.BOLD));
titleFormat.setBackground(Colour.VERY_LIGHT_YELLOW);
titleFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);

keyFormat = new WritableCellFormat(new WritableFont(WritableFont.createFont(font), fontSize, WritableFont.BOLD));
keyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

dataFormat = new WritableCellFormat(new WritableFont(WritableFont.createFont(font), fontSize));

try {
book = Workbook.createWorkbook(outStream);
if (fileName != null) {
this.response.addHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8")
+ "\"");
}
} catch (Exception e) {
log.error("Create Excel Failed: " + e);
e.printStackTrace();
}
}

public void addSheet(List dataList, String sheetName) throws Exception {
WritableSheet sheet = getNewSheet(sheetName);
if (dataList == null || dataList.size() == 0) {
return;
}

// 生成标题
Map firstItem = (Map) dataList.get(0);
Object[] keys = firstItem.keySet().toArray();
for (int j = 0; j < keys.length; j++) {
Label label = new Label(j, 0, (String) keys[j], keyFormat);
sheet.addCell(label);
}

// 生成数据行
for (int j = 0; j < dataList.size(); j++) {
Map item = (Map) dataList.get(j);
for (int k = 0; k < keys.length; k++) {
// 设置数据行位置
int lineNum = j + 1;
Object value = item.get(keys[k]);
Label label = new Label(k, lineNum, value == null ? "" : value.toString(), dataFormat);
sheet.addCell(label);
}
}
}

/**
* 添加数据部分
* @param dataList
* @param title
* @throws Exception
*/
public void addSheetPart(List dataList, String title) throws Exception {
partsList.add(dataList);
partsTitle.add(title);
}

public void addSheetFromPart(String sheetName) throws Exception {
WritableSheet sheet = getNewSheet(sheetName);
if (partsList.size() > 0) {
int lineCounter = 0;//行计数器

List dataList = null;
Label title = null;
Label label = null;

for (int i = 0; i < partsList.size(); i++) {
dataList = (List) partsList.get(i);
if (dataList.size() == 0) {
title = new Label(0, lineCounter, (String) partsTitle.get(i), titleFormat);
sheet.addCell(title);
lineCounter++;
label = new Label(0, lineCounter, "", dataFormat);
sheet.addCell(label);
lineCounter++;
continue;
}

// 获取标题信息
Map firstItem = (Map) dataList.get(0);
Object[] keys = firstItem.keySet().toArray();

// 写入Title
title = new Label(0, lineCounter, (String) partsTitle.get(i), titleFormat);
sheet.mergeCells(0, lineCounter, keys.length - 1, lineCounter);
sheet.addCell(title);
lineCounter++;

// 写入表头
for (int j = 0; j < keys.length; j++) {
label = new Label(j, lineCounter, (String) keys[j], keyFormat);
sheet.addCell(label);
}
lineCounter++;

// 生成数据行
for (int j = 0; j < dataList.size(); j++) {
Map item = (Map) dataList.get(j);
for (int k = 0; k < keys.length; k++) {
Object value = item.get(keys[k]);
label = new Label(k, lineCounter, value == null ? "" : value.toString(), dataFormat);
sheet.addCell(label);
}
lineCounter++;
}
}
partsList.clear();
partsTitle.clear();
}
}

/**
* 添加数据行
*/
public void addLine(String[] text) {
addLine(text, FMT_DATA, 1);
}

/**
* 添加数据行
*/
public void addLine(String[] text, int format) {
addLine(text, format, 1);
}

/**
* 添加数据行
*/
public void addLine(String[] text, int format, int colspan) {
if (format == FMT_TITLE) {
linesList.add(new LineInfo(text, titleFormat, colspan));
} else if (format == FMT_KEY) {
linesList.add(new LineInfo(text, keyFormat, colspan));
} else {
linesList.add(new LineInfo(text, dataFormat, colspan));
}
}

/**
* 将数据行中写入Sheet
*/
public void addSheetFromLine(String sheetName) throws Exception {
WritableSheet sheet = getNewSheet(sheetName);
if (linesList.size() > 0) {
LineInfo li = null;
Label label = null;

for (int i = 0; i < linesList.size(); i++) {
li = (LineInfo) linesList.get(i);

for (int j = 0; j < li.text.length; j++) {
label = new Label(j, i, li.text[j] == null ? "" : li.text[j], li.format);
if ((li.colspan > 1) && (li.text.length == 1)) {
sheet.mergeCells(0, i, li.colspan - 1, i);
}
sheet.addCell(label);
}
}
linesList.clear();
}
}

public void writeExcel() throws Exception {
book.write();
book.close();
}

private WritableSheet getNewSheet(String sheetName) {
WritableSheet sheet = book.createSheet(sheetName, sheetCounter++);
formatSheet(sheet);
return sheet;
}

private void formatSheet(WritableSheet sheet) {
SheetSettings ss = sheet.getSettings();
ss.setDefaultColumnWidth(20);
ss.setDefaultRowHeight(300);
}
}

/**
* Sheet行信息
*/
class LineInfo {
protected String[] text;
protected WritableCellFormat format;
protected int colspan;

public LineInfo(String[] text, WritableCellFormat format, int colspan) {
this.text = text;
this.format = format;
this.colspan = colspan;
}
}
有问题我qq160766790

Answer 2

过程大概如下：
List<Employee> list = new ArrayList<Employee>();//Employee为你的POJO类
this.list = this.getService().findAllEmployees(id[n]);//id为你选择的员工ID集合
Employee e= null;
String[][] data=new String[list .size()][m];//定义二维数据，列参数m即为字段数,已知值
这里需要把上面得到的list存储的对象值转换到二维数组data
for(int i=0;i<list.size();i++){
e= list.get(i);
data[i][0]=e.get**();//如果不是String类别要转换下再赋值,**代表具体字段名
data[i][1]=e.get**();//如果不是String类别要转换下再赋值
~~~~//依次赋值
data[i][m-1]=e.get**();//如果不是String类别要转换下再赋值
}
//使用JXL写入Excel
File outFile = new File(String str);//根据具体位置写
WritableWorkbook workbook = Workbook.createWorkbook(outFile);
WritableSheet sheet = workbook.createSheet("Employee ", 0);
Label lab = null;
for (int x = 0; x < data.length; x++) {
for (int y = 0; y < data[x].length; y++) {
lab = new Label(y, x, data[x][y]);
sheet.addCell(lab) ;
}
}
workbook.write() ;
workbook.close() ;