今天是最后一天了，废话就不多说了直接上干货吧！
1、java导出excel用到POI所有jar包 ，大家可以直接到下面地址下载点击打开链接
2、导出excel的方法
package org;import java.io.FileOutputStream;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFHeader;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.util.CellRangeAddress;public class ExportExcel {public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){try{//1.创建工作簿HSSFWorkbook workbook = new HSSFWorkbook();//1.1创建合并单元格对象CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);//起始行,结束行,起始列,结束列//班组与时间startCellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);//起始行,结束行,起始列,结束列//班组与时间end//标题CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7);//起始行,结束行,起始列,结束列//金额CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7);//起始行,结束行,起始列,结束列//负责人CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7);//起始行,结束行,起始列,结束列//说明CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddressinfo1 = new CellRangeAddress(userList.size()+8,userList.size()+8,0,7);//起始行,结束行,起始列,结束列CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7);//起始行,结束行,起始列,结束列//部项目经理部HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);//派工单HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true);//班组和时间HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);//标题样式HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true);//内容样式HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);//2.创建工作表HSSFSheet sheet = workbook.createSheet("派单");//2.1加载合并单元格对象sheet.addMergedRegion(callRangeAddress);sheet.addMergedRegion(callRangeAddress1);sheet.addMergedRegion(callRangeAddress20);sheet.addMergedRegion(callRangeAddress21);sheet.addMergedRegion(callRangeAddress22);sheet.addMergedRegion(callRangeAddress31);sheet.addMergedRegion(callRangeAddress32);sheet.addMergedRegion(callRangeAddress33);sheet.addMergedRegion(callRangeAddress34);sheet.addMergedRegion(callRangeAddress35);sheet.addMergedRegion(callRangeAddress36);sheet.addMergedRegion(callRangeAddress37);sheet.addMergedRegion(callRangeAddressnumber1);sheet.addMergedRegion(callRangeAddressnumber2);sheet.addMergedRegion(callRangeAddressPersion1);sheet.addMergedRegion(callRangeAddressPersion2);sheet.addMergedRegion(callRangeAddressPersion3);sheet.addMergedRegion(callRangeAddressinfo);sheet.addMergedRegion(callRangeAddressinfo1);sheet.addMergedRegion(callRangeAddressinfo2);//设置默认列宽sheet.setDefaultColumnWidth(15);//3.创建行//3.1创建头标题行;并且设置头标题HSSFRow row = sheet.createRow(0);HSSFCell cell = row.createCell(0);//加载单元格样式cell.setCellStyle(headStyle);cell.setCellValue("xxxx项目部");HSSFRow rower = sheet.createRow(1);HSSFCell celler = rower.createCell(0);//加载单元格样式celler.setCellStyle(erStyle);celler.setCellValue("派 工 单");HSSFRow rowsan = sheet.createRow(2);HSSFCell cellsan = rowsan.createCell(0);HSSFCell cellsan1 = rowsan.createCell(3);HSSFCell cellsan2 = rowsan.createCell(5);//加载单元格样式cellsan.setCellStyle(sanStyle);cellsan.setCellValue("协作单位：x施工一堆");cellsan1.setCellStyle(sanStyle);cellsan1.setCellValue("");cellsan2.setCellStyle(sanStyle);cellsan2.setCellValue("时间： 10月 20日");//3.2创建列标题;并且设置列标题HSSFRow row2 = sheet.createRow(3);String[] titles = {"序号","工作内容","用工总人数","工日数","","单价（元）","金额(元）","备注"};//""为占位字符串for(int i=0;i<titles.length;i++){HSSFCell cell2 = row2.createCell(i);//加载单元格样式cell2.setCellStyle(colStyle);cell2.setCellValue(titles[i]);}HSSFRow rowfour = sheet.createRow(4);String[] titlefour = {"普工用工数","技工用工数"};for(int i=0;i<titlefour.length;i++){HSSFCell cell2 = rowfour.createCell(i+3);//加载单元格样式cell2.setCellStyle(colStyle);cell2.setCellValue(titlefour[i]);}//4.操作单元格;将用户列表写入excelif(userList != null){int i=1;for(int j=0;j<userList.size();j++){//创建数据行,前面有两行,头标题行和列标题行HSSFRow row3 = sheet.createRow(j+5);HSSFCell cell0 = row3.createCell(0);cell0.setCellStyle(cellStyle);cell0.setCellValue(i++);HSSFCell cell1 = row3.createCell(1);cell1.setCellStyle(cellStyle);cell1.setCellValue(userList.get(j).getWorkCtx());HSSFCell cell2 = row3.createCell(2);cell2.setCellStyle(cellStyle);cell2.setCellValue(userList.get(j).getTotalHumanDays());HSSFCell cell3 = row3.createCell(3);cell3.setCellStyle(cellStyle);cell3.setCellValue(userList.get(j).getGwnNum());HSSFCell cell4 = row3.createCell(4);cell4.setCellStyle(cellStyle);cell4.setCellValue(userList.get(j).getTmnNum());HSSFCell cell5 = row3.createCell(5);cell5.setCellStyle(cellStyle);cell5.setCellValue(userList.get(j).getTotalHumanDays());HSSFCell cell6 = row3.createCell(6);cell6.setCellStyle(cellStyle);cell6.setCellValue(userList.get(j).getUnitAmount());HSSFCell cell7= row3.createCell(7);cell7.setCellStyle(cellStyle);cell7.setCellValue(userList.get(j).getUnitPrice());}}HSSFRow rownumber = sheet.createRow(userList.size()+5);HSSFCell cellnumber = rownumber.createCell(0);HSSFCell cellnumber1 = rownumber.createCell(3);//加载单元格样式cellnumber.setCellStyle(sanStyle);cellnumber.setCellValue("金额合计(大写)");cellnumber1.setCellStyle(sanStyle);cellnumber1.setCellValue("￥ 78 元； 大写：柒拾捌元整");HSSFRow rowpersion = sheet.createRow(userList.size()+6);HSSFCell cellpersion = rowpersion.createCell(0);HSSFCell cellpersion1 = rowpersion.createCell(3);HSSFCell cellpersion2 = rowpersion.createCell(5);//加载单元格样式cellpersion.setCellStyle(sanStyle);cellpersion.setCellValue("协作单位负责人：");cellpersion1.setCellStyle(sanStyle);cellpersion1.setCellValue("经办人：");cellpersion2.setCellStyle(sanStyle);cellpersion2.setCellValue("部门负责人：");HSSFRow rowinfo = sheet.createRow(userList.size()+7);HSSFCell cellinfo = rowinfo.createCell(0);cellinfo.setCellStyle(sanStyle);cellinfo.setCellValue("说明：1、本标工单一式两联，第一联为派工人（工长）存根，第二联用作结算。");HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);HSSFCell cellinfo1 = rowinfo1.createCell(0);cellinfo1.setCellStyle(sanStyle);cellinfo1.setCellValue("2、本标工单必须在用工当日签认，否则不予认可；三日内交合同处汇总。");HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);HSSFCell cellinfo2 = rowinfo2.createCell(0);cellinfo2.setCellStyle(sanStyle);cellinfo2.setCellValue("3、工日数填写精确到半个工日。");//5.输出workbook.write(fout);// workbook.close();//out.close();}catch(Exception e){e.printStackTrace();}}/*** * @param workbook* @param fontsize* @return 单元格样式*/private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {// TODO Auto-generated method stubHSSFCellStyle style = workbook.createCellStyle();//是否水平居中if(flag1){style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中}style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中//创建字体HSSFFont font = workbook.createFont();//是否加粗字体if(flag){font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);}font.setFontHeightInPoints(fontsize);//加载字体style.setFont(font);return style;}}
3、main方法
package org;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.List;public class MainOut {public static void main(String args[]){//模拟部分数据List<WorkSheetDetail> detail = new ArrayList<WorkSheetDetail>();WorkSheetDetail d1 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");WorkSheetDetail d4 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");WorkSheetDetail d5 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");detail.add(d1);detail.add(d2);detail.add(d3);detail.add(d4);detail.add(d5);try { FileOutputStream fout = new FileOutputStream("E:/students.xls");new ExportExcel().getValue(detail, fout);fout.close(); } catch (Exception e) { e.printStackTrace(); } }}
4、实体类
package org;public class WorkSheetDetail {//工作内容private String workCtx;// 用工人总数工日数 = gwnNum+tmnNumprivate Float totalHumanDays;//普工用工数 1-4小时为半天，4-8小时为一天;120每天private Integer gwnNum;//技工用工数 1-4小时为半天，4-8小时为一天；160每天private Integer tmnNum;// 单价（元）private Float unitPrice;// 金额(元） = gwnNum*120+tmnNum+160private Float unitAmount;// 备注private String notes; public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,Float unitAmount, String notes) {super();this.workCtx = workCtx;this.totalHumanDays = totalHumanDays;this.gwnNum = gwnNum;this.tmnNum = tmnNum;this.unitPrice = unitPrice;this.unitAmount = unitAmount;this.notes = notes;}public String getWorkCtx() {return workCtx;}public void setWorkCtx(String workCtx) {this.workCtx = workCtx;}public Float getTotalHumanDays() {return totalHumanDays;}public void setTotalHumanDays(Float totalHumanDays) {this.totalHumanDays = totalHumanDays;}public Integer getGwnNum() {return gwnNum;}public void setGwnNum(Integer gwnNum) {this.gwnNum = gwnNum;}public Integer getTmnNum() {return tmnNum;}public void setTmnNum(Integer tmnNum) {this.tmnNum = tmnNum;}public Float getUnitPrice() {return unitPrice;}public void setUnitPrice(Float unitPrice) {this.unitPrice = unitPrice;}public Float getUnitAmount() {return unitAmount;}public void setUnitAmount(Float unitAmount) {this.unitAmount = unitAmount;}public String getNotes() {return notes;}public void setNotes(String notes) {this.notes = notes;}}
5、效果图
