Java导出Excel表格数据

java后台数据导出Excel表格

方法一:

/**

* 导出excel数据

*/

@RequestMapping(value = "/exportData")

@ResponseBody

public Map<String,Object> exportData(HttpServletRequest request,HttpServletResponse response ,Enroll enroll)

{

Map<String,Object> map = new HashMap<String,Object>();

map.put("message", "0");

Activity activity = new Activity();

if(enroll != null){

try

{

activity.setId(enroll.getActivityId());

activity = activityService.findOneActivity(activity);

List<String> strList=new ArrayList<String>();

strList.add("报名ID");

strList.add("活动ID");

strList.add("姓名");

strList.add("性别");

strList.add("手机");

strList.add("身份证");

strList.add("提交时间");

strList.add("活动名称");

//String[] Title={"报名ID","活动ID","姓名","性别","手机","身份证"};

if((activity.getFormField5() != null) && (!"2".equals(activity.getFormField5()))){

strList.add(activity.getFormField5());

// activity.setFormField5("字段5");

}

if((activity.getFormField6() == null) || (!"2".equals(activity.getFormField6()))){

//activity.setFormField6("字段6");

strList.add(activity.getFormField6());

}

if((activity.getFormField7() == null) || (!"2".equals(activity.getFormField7()))){

//activity.setFormField7("字段7");

strList.add(activity.getFormField7());

}

if((activity.getFormField8() == null) || (!"2".equals(activity.getFormField8()))){

//activity.setFormField8("字段8");

strList.add(activity.getFormField8());

}

List<Enroll> enrollList = activityService.getEnrollInfo(enroll);

for(int i=0; i<enrollList.size(); i++){

enrollList.get(i).setOpenid(activity.getMainTitle());

}

//String[] Title={"报名ID","活动ID","姓名","性别","手机","身份证",activity.getFormField5(),activity.getFormField6(),activity.getFormField7(),activity.getFormField8(),"提交时间","活动名称"};

String result=exportExcel("活动报名信息.xls",strList, enrollList,response);

if("系统提示：Excel文件导出成功！".equals(result)){

map.put("message", 10);

}else{

map.put("message", 0);

}

}

catch (Exception e)

{

e.printStackTrace();

log.error("Controller Error ActivityController-> exportData " + e.getMessage());

}

}

return map;

}

/**

* 导出Excel

* @param fileName EXCEL文件名称

* @param listTitle EXCEL文件第一行列标题集合

* @param listContent EXCEL文件正文数据集合

* @return

*/

public String exportExcel(String fileName,List<String> Title, List<Enroll> listContent,HttpServletResponse response) {

String result="系统提示：Excel文件导出成功！";

// 以下开始输出到EXCEL

try {

//定义输出流，以便打开保存对话框______________________begin

OutputStream os = response.getOutputStream();// 取得输出流

response.reset();// 清空输出流

response.setHeader("Content-disposition", "attachment; filename="+ new String(fileName.getBytes("GB2312"),"ISO8859-1"));

// 设定输出文件头

response.setContentType("application/msexcel");// 定义输出类型

//定义输出流，以便打开保存对话框_______________________end

/** **********创建工作簿************ */

WritableWorkbook workbook = Workbook.createWorkbook(os);

/** **********创建工作表************ */

WritableSheet sheet = workbook.createSheet("Sheet1", 0);

/** **********设置纵横打印（默认为纵打）、打印纸***************** */

jxl.SheetSettings sheetset = sheet.getSettings();

sheetset.setProtected(false);

/** ************设置单元格字体************** */

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); // 文字是否换行

/** ***************以下是EXCEL开头大标题，暂时省略********************* */

//sheet.mergeCells(0, 0, colWidth, 0);

//sheet.addCell(new Label(0, 0, "XX报表", wcf_center));

/** ***************以下是EXCEL第一行列标题********************* */

for (int i = 0; i < Title.size(); i++) {

sheet.addCell(new Label(i, 0,Title.get(i),wcf_center));

}

/** ***************以下是EXCEL正文数据********************* */

Field[] fields=null;

int i=1;

for(Enroll obj:listContent){

fields=obj.getClass().getDeclaredFields();

int j=0;

for(Field v:fields){

v.setAccessible(true);

Object va=v.get(obj);

if(va==null){

va="";

}

sheet.addCell(new Label(j, i,va.toString(),wcf_left));

j++;

}

i++;

}

/** **********将以上缓存中的内容写到EXCEL文件中******** */

workbook.write();

/** *********关闭文件************* */

workbook.close();

} catch (Exception e) {

result="系统提示：Excel文件导出失败，原因："+ e.toString();

System.out.println(result);

e.printStackTrace();

}

return result;

}

方法二:

public void createorderExcel_List(List<List> ls, String fileName, HttpServletResponse response) throws Exception

{

// 创建Excel的工作书册 Workbook,对应到一个excel文档

HSSFWorkbook wb = new HSSFWorkbook();

// 创建Excel的工作sheet,对应到一个excel文档的tab

HSSFSheet sheet = wb.createSheet("sheet1");

int currentRow = 0;

currentRow = insertHeader(sheet, currentRow, getCellStyle(wb));

for (List temp : ls)

{

if (temp.size() > 1)

{

// 合并单元格(startRow，endRow，startColumn，endColumn)

// sheet.addMergedRegion(new CellRangeAddress(currentRow,

// currentRow+temp.size()-1, 0, 0));

for (Object value : temp)

{

currentRow = insertRow(sheet, currentRow, value);

}

}

else

{

// 创建Excel的sheet的一行

if (temp.size() > 0)

{

currentRow = insertRow(sheet, currentRow, temp.get(0));

}

}

}

response.setCharacterEncoding("UTF-8");

response.setContentType("application/csv");

// 防止文件名称乱码

response.setHeader("Content-disposition", "attachment;filename=" + new String((fileName + ".csv").getBytes("utf-8"), "iso-8859-1"));

OutputStream ouputStream = response.getOutputStream();

wb.write(ouputStream);

ouputStream.flush();

ouputStream.close();

}

public class ExportExcelUtil

{

// 第一个参数是表头list，第二个参数是表头style，第三个参数是内容list（将一个对象转换成list），第四个参数是表单内容的style，第四个参数是表单的名称,第五个参数是开始行

public HSSFWorkbook createExcel_List(List<String> headerName, HSSFCellStyle headerStyle, List<List<String>> contentLs, HSSFCellStyle contentStyle, String sheetName, Integer currentRow)

{

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet(StringUtil.isNullEmpty(sheetName) ? "sheet" : sheetName);

// 当前行数

if (currentRow == null)

{

currentRow = 0;

}

// 插入头部

if (headerName != null && headerName.size() > 0)

{

insertHeader(sheet, currentRow, headerName, (headerStyle == null ? getCellStyle(wb) : headerStyle));

}

// 插入内容

if (contentStyle != null)

{

if (contentLs != null && contentLs.size() > 0)

{

insertRows(sheet, currentRow, contentLs, contentStyle);

}

} else

{

if (contentLs != null && contentLs.size() > 0)

{

insertRows(sheet, currentRow, contentLs);

}

}

return wb;

}

/**

* 获取默认头部表格样式

*/

public HSSFCellStyle getCellStyle(HSSFWorkbook wb)

{

HSSFCellStyle style = null;

// 创建字体样式

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

// 创建单元格样式

style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setFont(font);

return style;

}

/**

* 插入表单头部

*/

public HSSFSheet insertHeader(HSSFSheet sheet, Integer currentRow, List<String> headerName, HSSFCellStyle style)

{

HSSFRow row = sheet.createRow(currentRow);

HSSFCell cell = null;

for (int i = 0; i < headerName.size(); i++)

{

cell = row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(headerName.get(i));

}

currentRow++;

return sheet;

}

/**

* 插入行内容(有样式)

*/

public HSSFSheet insertRows(HSSFSheet sheet, Integer currentRow, List<List<String>> contentLs, HSSFCellStyle contentStyle)

{

for (int i = 0; i < contentLs.size(); i++)

{

List<String> temp = contentLs.get(i);

HSSFRow row = sheet.createRow(currentRow);

row.setRowStyle(contentStyle);

for (int j = 0; j < temp.size(); j++)

{

String value = temp.get(j);

HSSFCell cell = row.createCell(j);

cell.setCellValue(value);

}

currentRow++;

}

return sheet;

}

/**

* 插入行内容(没有样式)

*/

public HSSFSheet insertRows(HSSFSheet sheet, Integer currentRow, List<List<String>> contentLs)

{

for (int i = 0; i < contentLs.size(); i++)

{

List<String> temp = contentLs.get(i);

HSSFRow row = sheet.createRow(currentRow);

for (int j = 0; j < temp.size(); j++)

{

String value = temp.get(j);

HSSFCell cell = row.createCell(j);

cell.setCellValue(value);

}

currentRow++;

}

return sheet;

}

// getHeaderCellStyle

public static HSSFCellStyle getHeaderCellStyle(HSSFWorkbook wb)

{

// 创建单元格样式

HSSFCellStyle style = wb.createCellStyle();

// 创建字体样式

HSSFFont font = wb.createFont();

// 粗体显示

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 水平居中

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 垂直居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

style.setFont(font);

return style;

}

// getContentCellStyle

public static HSSFCellStyle getContentCellStyle(HSSFWorkbook wb)

{

// 创建单元格样式

HSSFCellStyle style = wb.createCellStyle();

// 水平居中

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 垂直居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

return style;

}

}