Java数据导出Excel文件

java导出excel可以用POI或者jxl，本文使用的是jxl，代码备份如下，方便下次使用。

1.添加jar文件

import java.io.File;  import java.io.IOException;  import java.io.OutputStream;  import java.util.List;  import java.util.Map;    import javax.servlet.ServletException;  import javax.servlet.http.HttpServlet;  import javax.servlet.http.HttpServletRequest;  import javax.servlet.http.HttpServletResponse;    import com.esri.arcgis.display.NewTextBezierCurveFeedback;  import jxl.Workbook;  import jxl.format.Alignment;  import jxl.format.Border;  import jxl.format.BorderLineStyle;  import jxl.format.Colour;  import jxl.format.Pattern;  import jxl.write.Label;  import jxl.write.VerticalAlignment;  import jxl.write.WritableCellFormat;  import jxl.write.WritableFont;  import jxl.write.WritableSheet;  import jxl.write.WritableWorkbook;  import jxl.write.biff.RowsExceededException;

2.jxl对Excel表格的认识

（1）每个单元格的位置认为是由一个二维坐标(i，j)给定，其中i表示列，j表示行，并且从上到下递增，从左到右递增。
（2）对于合并单元格的以最左，最上的单元格的坐标为准。如下图中t.xls，一班名单(0,0)，陈茵(1,2)，陈开先(1,6)。

3.jsp页面代码

/* 导出Excel */function exportExcel(){window.open("...!exportExcel.do");}

4.java代码对.xls的读取

public void exportExcel(){HttpServletResponse response = ServletActionContext.getResponse();List<SgfxJxysZb> listExcel = xxxService.findExcel();//导出Excel数据查询String fileName = "导出excel名字" + ".xls";WritableWorkbook workbook = null;try {int maxRowNum = 60000;int totalPage = listExcel.size()/maxRowNum+1;workbook = Workbook.createWorkbook(response.getOutputStream());for (int j = 1; j <= totalPage; j++) {response.setContentType("application/vnd.ma-excel;charset=GBK");response.setHeader("Content-Disposition", "attachment;filename="+ new String(new String(fileName.getBytes("GBK"), "ISO8859_1")));WritableSheet sheet = workbook.createSheet("XXX"+ "-第" + j + "页",j-1);WritableFont font = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);WritableCellFormat center = new WritableCellFormat(font);center.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);center.setAlignment(jxl.format.Alignment.CENTRE);center.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);center.setWrap(true);sheet.setColumnView(0, 40);sheet.setColumnView(1, 50);sheet.addCell(new Label(0,0,"标题行1",center));sheet.addCell(new Label(1,0,"标题行2",center));sheet.addCell(new Label(2,0,"标题行3",center));sheet.addCell(new Label(3,0,"标题行4",center));sheet.addCell(new Label(4,0,"标题行5",center));DecimalFormat dFormat = new DecimalFormat("#.##");//使用百分比的时候用到SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");String sgsj ="";String zb="";String tb="";String hb ="";int k =0;if(j<totalPage){for (int i = (j-1)*maxRowNum; i < j*maxRowNum; i++) {sgsj =sdf1.format(listExcel.get(i).getStartTime())+"~"+sdf1.format(listExcel.get(i).getEndTime());sheet.addCell(new Label(0,k+1,sgsj,center));sheet.addCell(new Label(1,k+1,listExcel.get(i).getZhTjz(),center));if(listExcel.get(i).getZb()!=null&&!"".equals(listExcel.get(i).getZb())){if((listExcel.get(i).getZb())*100>=1000000){sheet.addCell(new Label(2,k+1,"MAX",center));}else{zb =dFormat.format((listExcel.get(i).getZb())*100);sheet.addCell(new Label(2,k+1,zb,center));}}else{sheet.addCell(new Label(2,k+1,"0.00",center));}if(listExcel.get(i).getTb()!=null&&!"".equals(listExcel.get(i).getTb())){if((listExcel.get(i).getTb())*100>=1000000){sheet.addCell(new Label(3,k+1,"MAX",center));}else{tb =dFormat.format((listExcel.get(i).getTb())*100);sheet.addCell(new Label(3,k+1,tb,center));}}else{sheet.addCell(new Label(3,k+1,"0.00",center));}if(listExcel.get(i).getHb()!=null&&!"".equals(listExcel.get(i).getHb())){if((listExcel.get(i).getHb())*100>=1000000){sheet.addCell(new Label(4, k+1, "MAX",center));}else{hb =dFormat.format((listExcel.get(i).getHb())*100);sheet.addCell(new Label(4, k+1, hb,center));}}else{sheet.addCell(new Label(4, k+1, "0.00",center));}k++;}}if(j==totalPage){for (int i = (j-1)*maxRowNum; i < listExcel.size(); i++) {sgsj =sdf1.format(listExcel.get(i).getStartTime())+"~"+sdf1.format(listExcel.get(i).getEndTime());sheet.addCell(new Label(0,k+1,sgsj,center));sheet.addCell(new Label(1,k+1,listExcel.get(i).getZhTjz(),center));if (listExcel.get(i).getZb() != null&& !"".equals(listExcel.get(i).getZb())) {if ((listExcel.get(i).getZb()) * 100 >= 1000000) {sheet.addCell(new Label(2, k + 1, "MAX", center));} else {zb = dFormat.format((listExcel.get(i).getZb()) * 100);sheet.addCell(new Label(2, k + 1, zb, center));}} else {sheet.addCell(new Label(2, k + 1, "0.00", center));}if(listExcel.get(i).getTb()!=null&&!"".equals(listExcel.get(i).getTb())){if((listExcel.get(i).getTb())*100>=1000000){sheet.addCell(new Label(3,k+1,"MAX",center));}else{tb =dFormat.format((listExcel.get(i).getTb())*100);sheet.addCell(new Label(3,k+1,tb,center));}}else{sheet.addCell(new Label(3,k+1,"0.00",center));}if(listExcel.get(i).getHb()!=null&&!"".equals(listExcel.get(i).getHb())){if((listExcel.get(i).getHb())*100>=1000000){sheet.addCell(new Label(4, k+1, "MAX",center));}else{hb =dFormat.format((listExcel.get(i).getHb())*100);sheet.addCell(new Label(4, k+1, hb,center));}}else{sheet.addCell(new Label(4, k+1, "0.00",center));}k++;}}workbook.write();}} catch (Exception e) {e.printStackTrace();} finally {if (workbook != null) {try {workbook.close();} catch (Exception e2) {e2.printStackTrace();}}}}

感觉步骤就这几步，1.导入需要的包；2.前台页面写出function；3.后台查询出需要导出的数据；4.按固定格式把数据填充到excel中。