java批量导出excel格式数据(含每条数据的附件)

class ExcelExport{public String exportExcel() throws Exception{//1：初始化数据//excel的标题数据（只有一条）ArrayList<String> fieldName = elecUserService.findFieldNameWithExcel();//excel的内容数据（多条）ArrayList<ArrayList<String>> fieldData = elecUserService.findFieldDataWithExcel(elecUser);//2：调用封装的POI报表的导出类ExcelFileGenerator.java，完成excel报表的导出ExcelFileGenerator excelFileGenerator = new ExcelFileGenerator(fieldName, fieldData);/**导出报表的文件名*/String filename = "用户报表（"+DateUtils.dateToStringWithExcel(new Date())+"）.xls";//处理乱码filename = new String(filename.getBytes("gbk"),"iso-8859-1");/**response中进行设置，总结下载，导出，需要io流和头*/response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+filename);response.setBufferSize(1024);//获取输出流OutputStream os = response.getOutputStream();excelFileGenerator.expordExcel(os);//使用输出流，导出return null;}}class ExcelFileGenerator {private final int SPLIT_COUNT = 15; //Excel每个工作簿的行数private ArrayList<String> fieldName = null; //excel标题数据集private ArrayList<ArrayList<String>> fieldData = null; //excel数据内容private HSSFWorkbook workBook = null;/*** 构造器* @param fieldName 结果集的字段名* @param data*/public ExcelFileGenerator(ArrayList<String> fieldName, ArrayList<ArrayList<String>>fieldData) {this.fieldName = fieldName;this.fieldData = fieldData;}/*** 创建HSSFWorkbook对象* @return HSSFWorkbook*/public HSSFWorkbook createWorkbook() {workBook = new HSSFWorkbook();//创建一个工作薄对象int rows = fieldData.size();//总的记录数int sheetNum = 0; //指定sheet的页数if (rows % SPLIT_COUNT == 0) {sheetNum = rows / SPLIT_COUNT;} else {sheetNum = rows / SPLIT_COUNT + 1;}for (int i = 1; i <= sheetNum; i++) {//循环2个sheet的值HSSFSheet sheet = workBook.createSheet("Page " + i);//使用workbook对象创建sheet对象HSSFRow headRow = sheet.createRow((short) 0); //创建行，0表示第一行（本例是excel的标题）for (int j = 0; j < fieldName.size(); j++) {//循环excel的标题HSSFCell cell = headRow.createCell( j);//使用行对象创建列对象，0表示第1列/**************对标题添加样式begin********************///设置列的宽度/sheet.setColumnWidth(j, 6000);HSSFCellStyle cellStyle = workBook.createCellStyle();//创建列的样式对象HSSFFont font = workBook.createFont();//创建字体对象//字体加粗font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体颜色变红font.setColor(HSSFColor.RED.index);//如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体cellStyle.setFont(font);/**************对标题添加样式end********************///添加样式cell.setCellType(HSSFCell.CELL_TYPE_STRING);if(fieldName.get(j) != null){//将创建好的样式放置到对应的单元格中cell.setCellStyle(cellStyle);cell.setCellValue((String) fieldName.get(j));//为标题中的单元格设置值}else{cell.setCellValue("-");}}//分页处理excel的数据，遍历所有的结果for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {if (((i - 1) * SPLIT_COUNT + k) >= rows)//如果数据超出总的记录数的时候，就退出循环break;HSSFRow row = sheet.createRow((short) (k + 1));//创建1行//分页处理，获取每页的结果集，并将数据内容放入excel单元格ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);for (int n = 0; n < rowList.size(); n++) {//遍历某一行的结果HSSFCell cell = row.createCell( n);//使用行创建列对象if(rowList.get(n) != null){cell.setCellValue((String) rowList.get(n).toString());}else{cell.setCellValue("");}}}}return workBook;}public void expordExcel(OutputStream os) throws Exception {workBook = createWorkbook();workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出os.close();}}。

import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;import jxl.Workbook;import jxl.format.UnderlineStyle;import bel;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;import ermodel.HSSFCell;import ermodel.HSSFCellStyle;import ermodel.HSSFRow;import ermodel.HSSFSheet;import ermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class ExcelOpt {/*** 生成一个Excel文件jxl* @param fileName 要生成的Excel文件名* @jxl.jar 版本：2.6*/public static void writeExcel(String fileName){WritableWorkbook wwb = null;try {//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象wwb = Workbook.createWorkbook(new File(fileName));} catch (IOException e) {e.printStackTrace();}if(wwb!=null){//创建一个可写入的工作表//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置WritableSheet ws = wwb.createSheet("工作表名称", 0);//下面开始添加单元格for(int i=0;i<10;i++){for(int j=0;j<5;j++){//这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行Label labelC = new Label(j, i, "这是第"+(i+1)+"行，第"+(j+1)+"列");try {//将生成的单元格添加到工作表中ws.addCell(labelC);} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}}try {//从内存中写入文件中wwb.write();//关闭资源，释放内存wwb.close();} catch (IOException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}}/*** 生成一个Excel文件POI* @param inputFile 输入模板文件路径* @param outputFile 输入文件存放于服务器路径* @param dataList 待导出数据* @throws Exception* @roseuid:*/public static void exportExcelFile(String inputFile,String outputFile,List dataList) throws Exception{//用模板文件构造poiPOIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(inputFile));//创建模板工作表HSSFWorkbook templatewb = new HSSFWorkbook(fs);//直接取模板第一个sheet对象HSSFSheet templateSheet = templatewb.getSheetAt(1);//得到模板的第一个sheet的第一行对象为了得到模板样式HSSFRow templateRow = templateSheet.getRow(0);//HSSFSheet timplateSheet = templatewb.getSheetAt(1);//取得Excel文件的总列数int columns = templateSheet.getRow((short) 0).getPhysicalNumberOfCells();// Debug.println("columns is : " + columns); //========================= //创建样式数组HSSFCellStyle styleArray[] = new HSSFCellStyle[columns];//一次性创建所有列的样式放在数组里for (int s = 0; s < columns; s++) {//得到数组实例styleArray[s] = templatewb.createCellStyle();}&nbsp循环对每一个单元格进行赋值//定位行for (int rowId = 1; rowId < dataList.size(); rowId++) {//依次取第rowId行数据每一个数据是valueListList valueList = (List) dataList.get(rowId - 1);//定位列for (int columnId = 0; columnId < columns; columnId++) {//依次取出对应与colunmId列的值//每一个单元格的值String dataValue = (String) valueList.get(columnId);//取出colunmId列的的style//模板每一列的样式HSSFCellStyle style = styleArray[columnId];//取模板第colunmId列的单元格对象//模板单元格对象HSSFCell templateCell = templateRow.getCell((short) columnId);//创建一个新的rowId行行对象//新建的行对象HSSFRow hssfRow = templateSheet.createRow(rowId);//创建新的rowId行columnId列单元格对象//新建的单元格对象HSSFCell cell = hssfRow.createCell((short) columnId);//如果对应的模板单元格样式为非锁定if (templateCell.getCellStyle().getLocked() == false) {//设置此列style为非锁定style.setLocked(false);//设置到新的单元格上cell.setCellStyle(style);}//否则样式为锁定else {//设置此列style为锁定style.setLocked(true);//设置到新单元格上cell.setCellStyle(style);}//设置编码cell.setEncoding(HSSFCell.ENCODING_UTF_16);//Debug.println("dataValue : " + dataV alue);//设置值统一为Stringcell.setCellValue(dataV alue);}}//设置输入流FileOutputStream fOut = new FileOutputStream(outputFile);//将模板的内容写到输出文件上templatewb.write(fOut);fOut.flush();//操作结束，关闭文件fOut.close();}/*** 导出数据为XLS格式* @param fos 生成Excel文件Path* @param bo 要导入的数据*/public static void writeExcelBo(String fos, java.util.List ve) {jxl.write.WritableWorkbook wwb;try{wwb= Workbook.createWorkbook(new File(fos));jxl.write.WritableSheet ws= wwb.createSheet("上市新书", 10);ws.addCell(new bel(0, 1, "书名"));ws.addCell(new bel(1, 1, "作者"));ws.addCell(new bel(2, 1, "定价"));ws.addCell(new bel(3, 1, "出版社"));int bookSize=ve.size();BookVO book = new BookVO();for (int i= 0; i < bookSize; i++){book= (BookVO)ve.get(i);ws.addCell(new bel(0, i + 2, "" + book.getBookName()));ws.addCell(new bel(1, i + 2, book.getBookAuthor()));ws.addCell(new bel(2, i + 2, "" + book.getBookPrice()));ws.addCell(new bel(3, i + 2, book.getBookConcern()));}// jxl.write.WritableFont wfc=//newjxl.write.WritableFont(WritableFont.ARIAL,255,WritableFont.BOLD,false,UnderlineStyle.NO_U NDERLINE,jxl.format.Colour.BLACK);//jxl.write.WritableCellFormat wcfFC= new jxl.write.WritableCellFormat(wfc);ws.addCell(new bel(0, 0, "2007年07月即将上市新书！"));wwb.write();// 关闭Excel工作薄对象wwb.close();} catch (IOException e){} catch (RowsExceededException e){} catch (WriteException e){}}public static void main(String[] args) {writeExcel("c:\\Test测试Excel.xls");System.out.println("OK");ArrayList list = new ArrayList();for (int i = 0; i < 10; i++) {BookVO book = new BookVO();book.setBookName("WebWork in action+"+i);book.setBookAuthor("唐勇+"+i);book.setBookPrice("39元+"+i);book.setBookConcern("飞思科技+"+i);list.add(book);}writeExcelBo("c:\\上市新书.xls",list);System.err.println("Book OK!!!");}}=================摘要=====================java如何操作Excel(数据导入导出)(转)jxl.jar，可以到下载。

java实现excel导出功能实现功能：java导出excel表1、jsp代码1 <form id="zhanwForm" action="<%=path%>/conferences.do?" target="_self" method="get" >2 <input type="hidden" name="method" value="outData"/>3 <input type="hidden" name="compassId" value='1'/>4 <input type="hidden" name="fromWhere" value="AAAA"/>56 </from>View Code2、js代码1 $(".daochu1").click(function(){2 var bank = new Array();3 $.each($(".guangGaoLeiXing").find("input:checked"), function () {4 bank.push($(this).val());5 });6if(bank == false){7 alert("请选择要统计类型");8 }else{9 $("#zhanwForm").submit();10 }11 })1213 $(".lyBtn").click(function(){14 $(".mask").show();15 $(".tishiDiv").show();16 $.ajax({17 url : '<%=path %>/conferences.do?method=getUserLy&fromWhere=csco',18 type : 'GET',19 dataType : 'json',20 success : function(data) {21 $(".mask").hide();22 $(".tishiDiv").hide();23 window.open(data.url);24 }25 })26 });View Code3、action处理代码1//导出数据2//SELECT SUBSTRING(create_time,1,10) s,COUNT(*) FROM data_read WHERE data_id IN (SELECT data_id FROM DATA WHERE model_id=2 OR model_id=27) GROUP BY s 3 @RequestMapping(params = "method=outData",method = RequestMethod.GET)4public void outCompassTongJiData(Integer compassId,String fromWhere,ModelMap model,HttpServletRequest request,HttpServletResponse response)5 {6 String urlPath = request.getScheme()+"://"+request.getServerName()+request.getContextPath();7 XSSFWorkbook rwb1 = new XSSFWorkbook();89while(true)10 {11if(rwb1.getNumberOfSheets() != 0){12 rwb1.removeSheetAt(0);13 }14else{15break;16 }17 }18try {19int bankLong = 0;20int bank1Long = 0;21 String [] bank = request.getParameterValues("bank");//⼴告类型选择22 String [] bank1 = request.getParameterValues("bank1");//⾝份选择23 String startTime = request.getParameter("startTime");24 String endTime = request.getParameter("endTime");25 String[] shenfenList = {"医⽣","医学⽣","其他"};26 String[] zhichengArray = {"主任医师","副主任医师","主治医师","住院医师（有执业证）","住院医师（未考执业证）","助理医师（有执业证）","助理医师（未考执业证）","其他"};27 String typeString1 ="";28 String typeString2 ="";29if(bank!=null){30 bankLong = bank.length;31for(int i=0;i<bankLong;i++){32if(bank[i].equals("1")||bank[i].equals("3")||bank[i].equals("8")){33 typeString1 = typeString1+bank[i]+",";34 }else {35 typeString2 = typeString2+bank[i]+",";36 }37 }38 }39if(typeString1 != null && typeString1.length() != 0){40 typeString1 = typeString1.substring(0, typeString1.length()-1);41 }42if(typeString2 != null && typeString2.length() != 0){43 typeString2 = typeString2.substring(0, typeString2.length()-1);44 }45if(bank1!=null){46 bank1Long = bank1.length;47 }4849 XSSFSheet ws1 = rwb1.createSheet("统计数据");50 ws1.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));51 ws1.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));52 ws1.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));53 ws1.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));54 ws1.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));55int count = 0;//⾏56int rolNUm = 0;//列5758 XSSFRow row1 = ws1.createRow(0);5960 row1.createCell(rolNUm).setCellValue("类型");61 rolNUm++;62 row1.createCell(rolNUm).setCellValue("ID");63 rolNUm++;64 row1.createCell(rolNUm).setCellValue("标题");65 rolNUm++;66 row1.createCell(rolNUm).setCellValue("地址");67 rolNUm++;68 row1.createCell(rolNUm).setCellValue("点击量");//1/26970 count++;71 XSSFRow row2 = ws1.createRow(count);72 row2.createCell(rolNUm).setCellValue("总点击次数");73 rolNUm++;74 row2.createCell(rolNUm).setCellValue("总点击⽤户数");75//六种统计类型：⾸页⼴告、上⽅⼴告、session相关性⼴告、展商活动、动态（现场秀）、消息站7677//得到需要统计的⼴告类型的集合78 List<CompassTongji> compassTongJis = conferencesService.getCompassTongJiByType(fromWhere,typeString1,typeString2,startTime,endTime);// i,group by dataId 查询不重复的data79//如果不为空，遍历80if(compassTongJis != null && compassTongJis.size() != 0){81for(CompassTongji compassTongJi :compassTongJis){82int dataId = compassTongJi.getDataId();//⼴告ID83int type = compassTongJi.getType();84 String title = compassTongJi.getDataTitle();//⼴告标题85 String url1 = compassTongJi.getDataUrl()==null?"":compassTongJi.getDataUrl();//⼴告地址86 String url = "";87if(type - 8 == 0){88if(url1.indexOf("method=")!= -1){89 String[] urlStrings = url1.split("&");90 url = urlStrings[0]+"&"+urlStrings[1];91 }else{92int index = url1.indexOf("?");93if(index != -1){94 url = url1.substring(0, index);95 }else{96 url = url1;97 }98 }99 }else{100int index = url1.indexOf("?");101if(index != -1){102 url = url1.substring(0, index);103 }else{104 url = url1;105 }106107 }108109//总点击量110int totalClickNumber = conferencesService.getTotalClickNumber(dataId,url,startTime,endTime,type,fromWhere);111//总点击⼈数112int totalClickPeopleNumber = conferencesService.getTotalClickPeopleNumber(dataId,url,startTime,endTime,type,fromWhere);113 count++;114 XSSFRow row3 = ws1.createRow(count);115 rolNUm=0;116 row3.createCell(rolNUm).setCellValue(type == 1?"⾸页⼴告":type ==2?"上⽅⼴告":type==3?"session相关性⼴告":type==4?"展商活动":type==5?"动态（现场秀）":type==6?"消息站":type == 7?"弹窗":type == 8?"推送":"⾸页117 rolNUm++;118 row3.createCell(rolNUm).setCellValue(dataId);119 rolNUm++;120 row3.createCell(rolNUm).setCellValue(title);121 rolNUm++;122 row3.createCell(rolNUm).setCellValue(url1);123 rolNUm++;124 row3.createCell(rolNUm).setCellValue(totalClickNumber);125 rolNUm++;126 row3.createCell(rolNUm).setCellValue(totalClickPeopleNumber);127for(int j=0;j<bank1Long;j++){128int tongJiZiDuan = Integer.valueOf(bank1[j]);129if (tongJiZiDuan == 1) {130//领域统计 userLy131//先查询所有领域列表132//遍历领域列表(compassfield) 根据领域id 查询⽤户表（field_id 领域ID field 领域）得到领域统计信息133 List<CompassField> compassList = conferencesService.getCompassList();134int compassListLength = compassList.size();135136 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, compassListLength+rolNUm));137 row1.createCell(rolNUm+1).setCellValue("领域");138if(compassList != null && compassList.size()!=0){139for (CompassField compassField : compassList) {140int compassFieldId = compassField.getCompassFieldId();141int countLy = conferencesService.getUserLyInfo(dataId,url,compassFieldId,startTime,endTime,type,fromWhere);142 rolNUm++;143 row2.createCell(rolNUm).setCellValue(compassField.getFieldName());144 row3.createCell(rolNUm).setCellValue(countLy);145 }146 }147 }148if(tongJiZiDuan == 2){//科室统计 userKs149 List<CompassKs> compassKsListList = conferencesService.getCompassKsList();150int userKsListLength = compassKsListList.size();151 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userKsListLength+rolNUm));152 row1.createCell(rolNUm+1).setCellValue("科室");153if(compassKsListList != null && compassKsListList.size()!=0){154for (CompassKs compassKs : compassKsListList) {155int compassKsId = compassKs.getCompassKsId();156int countKs = conferencesService.getUserKsInfo(dataId,url,compassKsId,startTime,endTime,type,fromWhere);157 rolNUm++;158 row2.createCell(rolNUm).setCellValue(compassKs.getKsName());159 row3.createCell(rolNUm).setCellValue(countKs);160 }161 }162 }163164if(tongJiZiDuan == 3){//职称统计 zhicheng165int userZhichengListLeng = zhichengArray.length;166 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userZhichengListLeng+rolNUm));167 row1.createCell(rolNUm+1).setCellValue("职称");168for (int k=0;k<userZhichengListLeng;k++) {169 String zhicheng = zhichengArray[k];170int userZhichengList = conferencesService.getUserZhichengInfo(dataId,url,startTime,endTime,zhicheng,type,fromWhere);171 rolNUm++;172 row2.createCell(rolNUm).setCellValue(zhicheng);173 row3.createCell(rolNUm).setCellValue(userZhichengList);174 }175176 }177178if(tongJiZiDuan == 4){//⾝份统计179int userShenfenListLength = shenfenList.length;180 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, userShenfenListLength + rolNUm));181 row1.createCell(rolNUm+1).setCellValue("⾝份");182183for (int k = 0;k<userShenfenListLength;k++) {184 String shenfen = shenfenList[k];185int countShenFen = conferencesService.getClickUserShenFenInfo( dataId,url,startTime,endTime,shenfen,type,fromWhere);186 rolNUm++;187 row2.createCell(rolNUm).setCellValue(shenfen);188 row3.createCell(rolNUm).setCellValue(countShenFen);189 }190 }191if(tongJiZiDuan == 5){//省市（市）统计192 List<Hospital> provinceList = conferencesService.getProvinceList();193194if(provinceList != null && provinceList.size()!=0){195for (Hospital hospital : provinceList) {196 Integer provinceId = hospital.getHospitalId();197 String provinceName = hospital.getProvince();198199if(provinceName!=null && provinceName.length() != 0){200 List<Hospital> cityList = conferencesService.getCityInfoByProvinceId(provinceId);201202if(cityList != null && cityList.size() != 0){203int cityListLength = cityList.size();204 ws1.addMergedRegion(new CellRangeAddress(0, 0, rolNUm+1, rolNUm+cityListLength));205 row1.createCell(rolNUm+1).setCellValue(provinceName);206for (Hospital hospital2 : cityList) {207//得到市的名称208 String cityName = hospital2.getCity();209 Integer cityId = hospital2.getHospitalId();210if(cityName!=null && cityName.length() != 0){211int cityNum = conferencesService.getCityInfoByProvinceNameAndCityName(dataId,url,startTime, endTime, cityId,type,fromWhere);212 rolNUm++;213 row2.createCell(rolNUm).setCellValue(cityName);214 row3.createCell(rolNUm).setCellValue(cityNum);215 }216 }217 }218 }//省不为空219 }220 }221 }//省市统计结束222 }223 }224 }//最外if结束225226227228 String filePath = request.getSession().getServletContext().getRealPath("files/execl");229 File conFile = new File(filePath); //⽬录结构230if(!conFile.exists())231 {232 conFile.mkdir();233 }234 String fileName = "compassTongJi.xlsx";235 File file = new File(filePath+"/"+fileName);236if(file.exists())237 {238 file.delete();239 }240else{241 file.createNewFile();242 }243 FileOutputStream fout = new FileOutputStream(file);244 rwb1.write(fout);245 fout.close();246//JSONObject jsonObject = new JSONObject();247//jsonObject.accumulate("url",request.getContextPath()+"/files/execl/"+fileName);248//writeToJson(response, jsonObject.toString());249 String urlString ="<script type='text/javascript'>window.parent.open('"+urlPath+"/files/execl/"+fileName+"')</script>";250 PrintWriter out = response.getWriter();251 response.setCharacterEncoding("utf-8");252 response.setContentType("text/html;charset=UTF-8");253 out.write(urlString);254 out.flush();255 out.close();256 } catch (Exception e) {257// TODO: handle exception258 e.printStackTrace();259 }260 }View Code梦想是⼀个说出来就矫情的东西，它是⽣长在暗地⾥的⼀颗种⼦，只有破⼟⽽出，拔节⽽长，终有⼀⽇开出花来，才能正⼤光明的让所有⼈都知道。

Java如何实现导出Excel功能，亲测有⽤！刚写了个导⼊Excel表格，现在来写个导出，其实形式都差不多，废话不多说，贴代码<div><button type="button" class="btn-btn" @click="exportData()">导出</button></div>这⾥根据个⼈需求，我这⾥写时间设置，根据条件去请求导出，这⾥因⼈⽽异exportData(){//判断⽤户有⽆填写操作类型if (this.type != "") {//默认全部操作类型，不传参this.param["code"] = this.code;}//判断⽤户有⽆填写开始⽇期if (this.startDate != '') {this.param['statrDate'] = new Date(this.startDate);}//判断⽤户有⽆填写结束⽇期if (this.endDate != '') {this.param['endDate'] = new Date(this.endDate);}this.param[this.optionSelected] = this.param.searchText;var url = "${ctx}/operatelog/exportOperateLog";window.open(url);}window.open(url);⼀开始请求我是使⽤ajax的，但是⼀直不弹出⽂件存放位置的选择，我怀疑是异步的问题，或者没设置好，后来使⽤这个语句，就会弹出⽂件存放位置的选择出来，没办法，实⼒有限，知道的可以留⾔告诉我下，谢谢！java中Controller层，来接受请求，数据库查询到的数据进⾏封装，然后使⽤ExcelUtils进⾏输出。

import com.axon.fable.empolderpackage.page.Pager;
import com.axon.fable.empolderpackage.string.MyPublic;
import mon.BaseAction;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.hibernato-generated method stub
return null;
}
public static String strNull(Object nullStr,String newStr,Integer cell){
if(nullStr==null||nullStr.equals("")){return newStr;}else{cell+=1;return nullStr+"";}
}
public static String getStr(String str,Integer cell){
if(str==null||str.trim().equals("")){return "";}else{cell+=1;return ","+str;}
private static String addr;
private static String addcomm;

java实现导出Excel（跨⾏，跨列）先来个最终结果样式：第⼀步：传参，后期可根据⾃⼰需要进⾏调整。

我这⾥需要的是quarter 代表季度dptid 部门编号根据接受过来的参数进⾏⽂档命名。

UserInfo userInfo=(UserInfo)request.getSession().getAttribute("userInfo");String userid=userInfo.getUserID();String quarter = request.getParameter("quarter");String dptid = request.getParameter("dptid");/***********************EXCEL导出部分**************************/String str3 = FileInfoTools.getSystemFilePath()+ "/documentTemp/";File file = new File(str3);if (!file.exists() && !file.isDirectory()) {file.mkdir();}String names = new SimpleDateFormat("yyyyMMddhhmmssSSS").format(new Date());String ourl = str3 + names + "_"+userid+".xls";FileOutputStream fos = null;String fileName = "";try {HSSFWorkbook wb = new HSSFWorkbook();//创建⼯作薄fileName = "安全可控导出"+"_"+userid+"_"+names+".xls";DBManager dbm = new DBManager();System.out.println("quarter+"+quarter+"dptid="+dptid);try{dbm.newConnection();String str = "select departmentname from dpt_department where departmentid='"+dptid+"'"; //获取唯⼀idString dptName = dbm.executeQueryString(str);fileName=dptName+"部门安全可控清单汇总"+"_"+userid+"_"+names+".xls";//创建sheet页,并写⼊内容createSheet(dbm,wb,getCardInfo(dbm,quarter,dptid));}catch(Exception e){e.printStackTrace();}finally{dbm.closeConnection();}File ff = new File(ourl);if (ff.exists()) {ff.delete();}fos = new FileOutputStream(ourl);wb.write(fos);fos.close();String u = "/project/system/fileAction.do?filePath=" + URLEncoder.encode(ourl)+ "&fileName=" + URLEncoder.encode(fileName);response.sendRedirect(u);} catch (Exception e) {e.printStackTrace();}第⼆步:创建sheet页签public void createSheet(DBManager dbm,HSSFWorkbook w,Vector vt) throws Exception{for(int i=0;i<vt.size();i++){Hashtable ht = (Hashtable)vt.get(i);String id = (String)ht.get("id");String quarter = (String)ht.get("quarter");HSSFSheet sheet = w.createSheet("安全可控清单");//写⼊数据wirteSheet(dbm,sheet,w,id,quarter);}}我这⾥页签是固定的名称可根据⾃⼰的需要根据参数来进⾏判断页签。

最简单的Java导出Excel中的数据普通的MS Office Excel只能在本地磁盘上打开和编辑保存，这使得程序员在开发项目时受到很多的约束，许多的功能无法实现或者无法达到理想的效果。

但是通过调用PageOffice开发平台，不仅可以在线打开并保存Excel文件还可以提交Excel中的数据。

下面我就简单的和大家分享一下如何实现Excel文档的在线打开、编辑、保存和数据的提交。

第一步：拷贝文件到WEB项目的“WEB-INF/lib”目录下。

拷贝PageOffice 示例中下的“WEB-INF/lib”路径中的pageoffice.cab和pageoffice.jar到新建项目的“WEB-INF/lib”目录下。

第二步：修改WEB项目的配置文件。

将如下代码添加到配置文件中：<!-- PageOffice Begin --><servlet><servlet-name>poserver</servlet-name><servlet-class>com.zhuozhengsoft .pageoffice.poserver.Server</servlet-class></servlet><servlet-mapping><servlet-name>poserver</servlet-name><url-pattern>/poserver.do</url-pattern></servlet-mapping><servlet-mapping><servlet-name>poserver</servlet-name><url-pattern>/pageoffice.cab</url-pattern></servlet-mapping><servlet-mapping><servlet-name>poserver</servlet-name><url-pattern>/popdf.cab</url-pattern></servlet-mapping><servlet-mapping><servlet-name>poserver</servlet-name><url-pattern>/sealsetup.exe</url-pattern></servlet-mapping><servlet><servlet-name>adminseal</servlet-name><servlet-class>com.zhuozhengsoft.pageoffice.poserver.AdminSeal </servlet-class></servlet><servlet-mapping><servlet-name>adminseal</servlet-name><url-pattern>/adminseal.do</url-pattern></servlet-mapping><servlet-mapping><servlet-name>adminseal</servlet-name><url-pattern>/loginseal.do</url-pattern></servlet-mapping><servlet-mapping><servlet-name>adminseal</servlet-name><url-pattern>/sealimage.do</url-pattern></servlet-mapping><mime-mapping><extension>mht</extension><mime-type>message/rfc822</mime-type></mime-mapping><context-param><param-name>adminseal-password</param-name><param-value>123456</param-value></context-param><!-- PageOffice End -->第三步：添加引用。

jxl.jar下载地址：/index_dodownloadInfo_loadid_6.shtmlpublic class ExportExcel {public static boolean exportExcel(HttpServletResponse response, List list) {try {OutputStream os = response.getOutputStream();// 取得输出流response.reset();// 清空输出流response.setHeader("Content-disposition","attachment;filename="+CreateOrderCode.getInstance().createOrderCode()+ ".xls");// 设定输出文件头response.setContentType("application/msexcel");// 定义输出类型WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件String tmptitle = "财务结算"; // 标题WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称// 设置excel标题WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);WritableCellFormat wcfFC = new WritableCellFormat(wfont); wcfFC.setBackground(Colour.AQUA);wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);wcfFC = new WritableCellFormat(wfont);// 开始生成主体内容wsheet.addCell(new Label(0, 2, "订单号"));wsheet.addCell(new Label(1, 2, "产品名称"));wsheet.addCell(new Label(2, 2, "数量"));wsheet.addCell(new Label(3, 2, "单价"));wsheet.addCell(new Label(4, 2, "成交价"));wsheet.addCell(new Label(5, 2, "购买人（会员）"));wsheet.addCell(new Label(6, 2, "购买时间"));for (int i = 0; i < list.size(); i++) {Object[] pate = (Object[])list.get(i);wsheet.addCell(new Label(0, i + 3, pate[0])); wsheet.addCell(new Label(1, i + 3, pate[1])); wsheet.addCell(new Label(2, i + 3, pate[2])); wsheet.addCell(new Label(3, i + 3, pate[3])); wsheet.addCell(new Label(4, i + 3, pate[4])); wsheet.addCell(new Label(5, i + 3, pate[5])); wsheet.addCell(new Label(6, i + 3, pate[6])); }// 主体内容生成结束wbook.write(); // 写入文件wbook.close();os.close(); // 关闭流return true;} catch (Exception ex) {ex.printStackTrace();return false;}}}。

java批量导⼊导出⽂件的实例分享（兼容xls,xlsx）⼀、介绍利⽤java实现⽂件的导⼊导出数据库，⽬前在⼤部分系统中是⽐较常见的功能了，今天写个⼩demo来理解其原理，没接触过的同学也可以看看参考下。

⽬前我所接触过的导⼊导出技术主要有POI和iReport，poi主要作为⼀些数据批量导⼊数据库，iReport做报表导出。

另外还有jxl类似poi的⽅式，不过貌似很久没跟新了，2007之后的office好像也不⽀持，这⾥就不说了。

⼆、POI使⽤详解2.1 什么是Apache POI？Apache POI是Apache软件基⾦会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2.2 POI的jar包导⼊本次讲解使⽤maven⼯程，jar包版本使⽤poi-3.14和poi-ooxml-3.14。

⽬前最新的版本是3.16。

因为3.15以后相关api有更新，部分操作可能不⼀样，⼤家注意下。

<!-- poi的包 3.15版本后单元格类型获取⽅式有调整 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14</version></dependency>2.3 POI的API讲解2.3.1 结构HSSF －提供读写Microsoft Excel格式档案的功能。

java导出Excel⽂件的步骤全纪录⼀、背景当前B/S模式已成为应⽤开发的主流，⽽在企业办公系统中，常常有客户这样⼦要求：你要把我们的报表直接⽤Excel打开(电信系统、银⾏系统)。

或者是：我们已经习惯⽤Excel打印。

这样在我们实际的开发中，很多时候需要实现导⼊、导出Excel的应⽤。

最近在java上做了⼀个EXCEL的导出功能，写了⼀个通⽤类，在这⾥分享分享，该类⽀持多sheet，且⽆需⼿动进⾏复杂的类型转换，只需提供三个参数即可：1、fileNameexcel⽂件名2、HasMap<String,List<?>> data具体的数据，每个List代表⼀张表的数据，？表⽰可为任意的⾃定义对象3、LinkedHashMap<String,String[][]> headersStirng代表sheet名。

每个String[][]代表⼀个sheet的定义，举个例⼦如下：String[][] header = {{"field1","参数1"}，{"field2","参数2"}，{"field3","参数3"}}其中的field1，field2，field3为对象中的属性名，参数1，参数2，参数3为列名，实际上这个指定了列的名称和这个列⽤到数据对象的哪个属性。

⼆、怎么⽤以⼀个例⼦来说明怎么⽤，假设有两个类A和B定义如下：public class A{private String name;private String address;}public class B{private int id;private double sum;private String cat;}现在我们通过查询数据库获得了A和B的两个列表：List<A> dataA = .....;List<B> dataB = .....;我们将这两个导出到excel中，⾸先需要定义sheet：String[][] sheetA = {{"name","姓名"},{"address","住址"}}String[][] sheetB = {{"id","ID"},{"sum","余额"},{"cat","猫的名字"}}然后将数据汇总构造⼀个ExcelUtil：String fileName = "测试Excel";HashMap<String,List<?>> data = new HashMap<>();//ASheet为表名，后⾯headers⾥的key要跟这⾥⼀致data.put("ASheet",dataA);data.put("BSheet",dataB);LinkedHashMap<String,String[][]> headers = new LinkedHashMap<>();headers.put("ASheet",sheetA);headers.put("BSheet",sheetB);ExcelUtil excelUtil = new ExcelUtil(fileName,data,headers);//获取表格对象HSSFWorkbook workbook = excelUtil.createExcel();//这⾥内置了⼀个写到response的⽅法（判断浏览器类型设置合适的参数），如果想写到⽂件也是类似的workbook.writeToResponse(workbook,request,response);当然通常数据是通过数据库查询的，这⾥为了演⽰⽅便没有从数据库查找。

JAVA导出EXCEL表格hi~ 好久不见⼩伙伴们，最近⽐较忙，没什么时间写，今天给⼤家分享的是JAVA如何导出EXCEL表格，因为最近有做这样⼀个功能，所以分享出来，如有不对之处，敬请指正。

在许多企业办公系统中，经常会有⽤户要求，需要对数据进⾏统计并且可以直接下载Excel⽂件，这样⼦的话，既然客户提出了要求，我们就应该去满⾜吖，毕竟客户是上帝嘛，那么我们如何去实现呢？且看我为你⼀⼀道来。

POI简介：Jakarta POI 是⼀套⽤于访问微软格式⽂档的Java API。

Jakarta POI有很多组件组成，其中有⽤于操作Excel格式⽂件的HSSF和⽤于操作Word的HWPF，在各种组件中⽬前只有⽤于操作Excel的HSSF相对成熟。

官⽅主页，API⽂档现在⽤的⽐较多的都是⽤POI技术来导出或者导⼊Excel，所以我们就⽤POI吧，⽤POI导出Excel我们⾸先要下载所需的jar包然后导⼊到我们的项⽬中，⽤maven的同学只需找到相关依赖加⼊到pom.xml⾥⾯即可。

1. 下载jar包：官⽅下载：这⾥可以下载到它的最新版本和⽂档，⽬前最新版本是3.7，这⾥使⽤⽐较稳定的3.6版。

百度⽹盘下载：https:///s/1mjhoaWK 密码：pkur2. 将jar包加⼊到项⽬中:将下载好的jar包加⼊到WEBINFO⽬录下的lib⽂件夹中，Eclipse⽤户选中jar包然后右击选择Build Path选项， Idea⽤户选中jar包然后右击选择Add as Library选项即可。

如果是⽤maven的可⾃⾏到maven中央仓库搜索poi然后选择对应的版本即可，也可以直接将下⾯代码复制到pom.xml。

<!-- https:///artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.6</version></dependency>⼩提⽰：⽤maven引⼊依赖jar包的可能会遇到包引⽤不到的bug，但是maven依赖确实已经引⼊了，⽽且没有任何报错，但是只要⼀引⽤ermodel下⾯的类就会报错，报错内容为：Caused by: ng.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook。

Cell cell1=sheet.getCell(0,0); String result=cell1.getContents(); System.out.println(result); book.close(); }catch(Exception e) { System.out.println(e); } } } 程序执行结果：test
对全部高中资料试卷电气设备，在安装过程中以及安装结束后进行高中资料试卷调整试验；通电检查所有设备高中资料电试力卷保相护互装作置用调与试相技互术关，系电，力根通保据过护生管高产线中工敷资艺设料高技试中术卷资，配料不置试仅技卷可术要以是求解指，决机对吊组电顶在气层进设配行备置继进不电行规保空范护载高与中带资负料荷试下卷高问总中题体资，配料而置试且时卷可，调保需控障要试各在验类最；管大对路限设习度备题内进到来行位确调。保整在机使管组其路高在敷中正设资常过料工程试况中卷下，安与要全过加，度强并工看且作护尽下关可都于能可管地以路缩正高小常中故工资障作料高；试中对卷资于连料继接试电管卷保口破护处坏进理范行高围整中，核资或对料者定试对值卷某，弯些审扁异核度常与固高校定中对盒资图位料纸置试，.卷保编工护写况层复进防杂行腐设自跨备动接与处地装理线置，弯高尤曲中其半资要径料避标试免高卷错等调误，试高要方中求案资技，料术编试交写5、卷底重电保。要气护管设设装线备备置敷4高、调动设中电试作技资气高，术料课中并3中试、件资且包卷管中料拒含试路调试绝线验敷试卷动槽方设技作、案技术，管以术来架及避等系免多统不项启必方动要式方高，案中为；资解对料决整试高套卷中启突语动然文过停电程机气中。课高因件中此中资，管料电壁试力薄卷高、电中接气资口设料不备试严进卷等行保问调护题试装，工置合作调理并试利且技用进术管行，线过要敷关求设运电技行力术高保。中护线资装缆料置敷试做设卷到原技准则术确：指灵在导活分。。线对对盒于于处调差，试动当过保不程护同中装电高置压中高回资中路料资交试料叉卷试时技卷，术调应问试采题技用，术金作是属为指隔调发板试电进人机行员一隔，变开需压处要器理在组；事在同前发一掌生线握内槽图部内 纸故，资障强料时电、，回设需路备要须制进同造行时厂外切家部断出电习具源题高高电中中源资资，料料线试试缆卷卷敷试切设验除完报从毕告而，与采要相用进关高行技中检术资查资料和料试检，卷测并主处且要理了保。解护现装场置设。备高中资料试卷布置情况与有关高中资料试卷电气系统接线等情况，然后根据规范与规程规定，制定设备调试高中资料试卷方案。

java导出Excel⼤数据量，⾃⼰经验总结！（⼆）在上⼀次的基础上加上了样式，以及中⽂列名1package com.tommy.fundation.util;23import java.io.OutputStream;4import java.util.ArrayList;5import java.util.HashMap;6import java.util.Iterator;7import java.util.List;8import java.util.Map;9import java.util.Set;1011import javax.servlet.http.HttpServletResponse;1213import ermodel.HSSFWorkbook;14import ermodel.HSSFSheet;15import ermodel.HSSFCellStyle;16import ermodel.HSSFFont;17import ermodel.HSSFRow;18import ermodel.HSSFCell;19import ermodel.CellStyle;20import ermodel.Font;21import ermodel.IndexedColors;2223public class ExportExcel<T> {24public void doExcel(HttpServletResponse response, List<T> list, String fileName) throws Exception {25 String[] columnNames = new String[0];26 doExcel(response, list , columnNames, fileName);27 }28/**29 * 导出多张excel表,解决xls格式⾏数65535的限制30 * @author OnlyOne31 * @param response32 * @param list 需要处理的list数据集合33 * @param columnNames 与实体属性⼀⼀对应的列名34 * @param fileName ⽂件名35 * @throws Exception36*/37public void doExcel(HttpServletResponse response, List<T> list, String[] columnNames, String fileName) throws Exception {38 OutputStream os = response.getOutputStream();//获取输出流39 response.reset();40// 设置下载头部信息。

使用java将数据导出EXCEL 1.效果图2.Struts2配置及代码2.1.配置<action name="user_*" class="userAction" method="{1}"> <resultname="userList_page">/pages/user_page/userList.jsp</resu lt><result name="outToExcel" type="stream"><param name="inputName">inputStream</param><paramname="contentDisposition">attachment;filename="${fileNam </param></result></action>2.2.action代码private UserService<User> userService;private InputStream inputStream;private String fileName;private List<User> users;//注：下面属性提供get*、set*方法//调用Excel(自己定义，下面有)类方法public String outUsersToExcel(){Excel excel=new Excel();//要显示表格列名excel.setColumns(new String[]{"序号","姓名","性别","地址","创建时间"});//生成EXCEL文件名称excel.setFileName("用户信息.xls");//文件表的头部标题excel.setTitle("用户信息");//将要导出的用户信息List<User> userList=userService.getAllUsers();List list=excel.getExcelContentList();int i=1;for(User user:userList){String[] row={i+"",user.getUserName()+"",user.getSex()+"",user.getAddress(),TimeUtil.getStrByDate(user.getCreateTime()) };i++;list.add(row);//设置序号if(i==Integer.parseInt(excel.getPerSheetRows())+1){ i=1;}}excel.setExcelContentList(list);//导出EXCELthis.inputStream=excel.outExcel(excel,this.inputStream,this .request);this.fileName=excel.getExcelFileName(excel.getFileName());return"outToExcel";}3.Excel属性类（自己定义，为了简化代码）public class Excel {public static Logger logger=Logger.getLogger(Excel.class);//Excel构造方法(初始化信息)@SuppressWarnings("unchecked")public Excel(){propertiesTool=new=PropertiesTool("system.properties;excelContentList=new ArrayList();}private PropertiesTool propertiesTool;//系统属性文件private String title;//文件内容标题private String filePath;//生成EXCEL的路径private String folderName;//存放EXCEL文件的文件夹名称private String perSheetRows;//每张表显示的数量private String[] columns;//EXCEL信息列的名称private String condition;//查询的条件private String fileName;//文件名称@SuppressWarnings("unchecked")private List excelContentList;//导出数据(格式化后的String数组形式的集合)注：上面Excel中的属性自己要提供get*、set*方法//导出Excelpublic InputStream outExcel(Excel excel,InputStreaminputStream,HttpServletRequest request){// OutToExcel里面有导出Excel具体方法的实现，下面具体会结束OutToExcel outToExcel=new OutToExcel();//得到系统真实路径String realPath =request.getSession().getServletContext().getRealPath("/");//生成EXCEL的路径excel.setFilePath(realPath+excel.getFolderName());try {//调用方法转化为EXCEL表outToExcel.outExcel(excel);//下载时提示的文件名。

java 导出excel数据的方法主题：Java 导出Excel 数据的方法引言：在开发Java应用程序时，经常需要将数据导出到Excel文件中。

Excel是一种常用的电子表格工具，可以对数据进行分析和可视化展示。

本文将介绍如何使用Java来导出Excel数据，以及实现这一功能的详细步骤。

1. 导入所需库首先，我们需要在Java项目中导入所需的库。

常用的库包括Apache POI和Apache POI-OOXML。

这些库提供了用于操作Excel 文件的类和方法。

2. 创建工作簿和工作表在Java中，我们通过HSSFWorkbook类创建一个新的Excel 工作簿。

然后，使用createSheet方法创建一个新的工作表对象。

我们可以为工作表指定名称，并将其附加到工作簿中。

3. 创建表头在工作表中，我们可以使用createRow方法创建一个新的行对象。

然后，使用createCell方法在行中创建单元格对象。

通过设置单元格的值，可以定义表头的内容。

4. 导出数据接下来，我们可以使用循环遍历数据集，并将数据写入Excel文件中。

每个数据项都可以被写入单元格对象中。

可以根据需要设置单元格的格式和样式。

5. 保存文件当所有数据都被写入Excel文件后，我们可以使用FileOutputStream类将工作簿中的数据保存到硬盘上的某个位置。

通过指定文件路径和文件名，可以将Excel文件保存在所需的位置。

6. 完整代码示例javaimport ermodel.HSSFWorkbook;import ermodel.*;import java.io.FileOutputStream;import java.util.List;public class ExcelExporter {public static void export(List<DataObject> data, String filePath) {try {Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet 1");创建表头Row headerRow = sheet.createRow(0);Cell cell = headerRow.createCell(0);cell.setCellValue("Column 1");cell = headerRow.createCell(1);cell.setCellValue("Column 2");...导出数据int rowNum = 1;for (DataObject obj : data) {Row row = sheet.createRow(rowNum++);cell = row.createCell(0);cell.setCellValue(obj.getField1());cell = row.createCell(1);cell.setCellValue(obj.getField2());...}保存文件FileOutputStream fileOut = new FileOutputStream(filePath);workbook.write(fileOut);fileOut.close();System.out.println("Excel文件导出成功！");} catch (Exception e) {e.printStackTrace();}}}示例中，我们通过export方法将一个包含数据对象的列表导出到Excel文件中。

java实现导出Excel的功能导出excel是咱Java开发的必备技能啦，之前项⽬有这个功能，现在将其独⽴出来，分享⼀下。

所⽤技术就是SpringBoot，然后是MVC架构模式。

废话不多说，直接上代码了，源码点末尾链接就可以下载。

（2）修改pom⽂件，添加依赖；<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- 导出excel --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version></dependency><!-- https:///artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14</version></dependency><!-- https:///artifact/org.apache.poi/poi-contrib --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-contrib</artifactId><version>3.6</version></dependency><!-- https:///artifact/org.apache.poi/poi-ooxml-schemas --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17</version></dependency><!-- https:///artifact/org.apache.poi/poi-scratchpad --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.17</version></dependency>（3）新建⼀个实体类，命名为User.java；package com.twf.springcloud.ExportExcel.po;import java.io.Serializable;public class User implements Serializable{private static final long serialVersionUID = -9180229310895087286L;private String name; // 姓名private String sex; // 性别private Integer age; // 年龄private String phoneNo; // ⼿机号private String address; // 地址private String hobby; // 爱好public User(String name, String sex, Integer age, String phoneNo, String address, String hobby) {super(); = name;this.sex = sex;this.age = age;this.phoneNo = phoneNo;this.address = address;this.hobby = hobby;}public String getName() {return name;}public void setName(String name) { = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public String getPhoneNo() {return phoneNo;}public void setPhoneNo(String phoneNo) {this.phoneNo = phoneNo;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getHobby() {return hobby;}public void setHobby(String hobby) {this.hobby = hobby;}@Overridepublic String toString() {return "User [name=" + name + ", sex=" + sex + ", age=" + age + ", phoneNo=" + phoneNo + ", address=" + address + ", hobby=" + hobby + "]";}}（4）新建⼀个excel样式⼯具类；package com.twf.springcloud.ExportExcel.utils;import ermodel.HSSFCellStyle;import ermodel.HSSFFont;import ermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import ermodel.CellStyle;import ermodel.Font;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;/*** excle样式⼯具类*/public class ExcelFormatUtil {/*** 设置报表头样式* @param workbook* @return*/public static CellStyle headSytle(SXSSFWorkbook workbook){// 设置style1的样式，此样式运⽤在第⼆⾏CellStyle style1 = workbook.createCellStyle();// cell样式// 设置单元格背景⾊，设置单元格背景⾊以下两句必须同时设置style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充⾊// 设置单元格上、下、左、右的边框线style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);style1.setBorderRight(HSSFCellStyle.BORDER_THIN);style1.setBorderTop(HSSFCellStyle.BORDER_THIN);Font font1 = workbook.createFont();// 创建⼀个字体对象font1.setBoldweight((short) 10);// 设置字体的宽度font1.setFontHeightInPoints((short) 10);// 设置字体的⾼度font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显⽰style1.setFont(font1);// 设置style1的字体style1.setWrapText(true);// 设置⾃动换⾏style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显⽰居中（左右⽅向）style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显⽰居中(上下⽅向)return style1;}/*** 设置报表体样式* @param wb* @return*/public static CellStyle contentStyle(SXSSFWorkbook wb){// 设置style1的样式，此样式运⽤在第⼆⾏CellStyle style1 = wb.createCellStyle();// cell样式// 设置单元格上、下、左、右的边框线style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);style1.setBorderRight(HSSFCellStyle.BORDER_THIN);style1.setBorderTop(HSSFCellStyle.BORDER_THIN);style1.setWrapText(true);// 设置⾃动换⾏style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显⽰居中（左右⽅向）style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显⽰居中(上下⽅向)return style1;}/*** 设置报表标题样式* @param workbook* @return*/public static HSSFCellStyle titleSytle(HSSFWorkbook workbook,short color,short fontSize){// 设置style1的样式，此样式运⽤在第⼆⾏HSSFCellStyle style1 = workbook.createCellStyle();// cell样式// 设置单元格背景⾊，设置单元格背景⾊以下两句必须同时设置//style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式//short fcolor = color;if(color != HSSFColor.WHITE.index){style1.setFillForegroundColor(color);// 设置填充⾊}// 设置单元格上、下、左、右的边框线style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);style1.setBorderRight(HSSFCellStyle.BORDER_THIN);style1.setBorderTop(HSSFCellStyle.BORDER_THIN);HSSFFont font1 = workbook.createFont();// 创建⼀个字体对象font1.setBoldweight(fontSize);// 设置字体的宽度font1.setFontHeightInPoints(fontSize);// 设置字体的⾼度font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显⽰style1.setFont(font1);// 设置style1的字体style1.setWrapText(true);// 设置⾃动换⾏style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显⽰居中（左右⽅向）style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显⽰居中(上下⽅向) return style1;}/***设置表头* @param sheet*/public static void initTitleEX(SXSSFSheet sheet, CellStyle header,String title[],int titleLength[]) {SXSSFRow row0 = sheet.createRow(0);row0.setHeight((short) 800);for(int j = 0;j<title.length; j++) {SXSSFCell cell = row0.createCell(j);//设置每⼀列的字段名cell.setCellValue(title[j]);cell.setCellStyle(header);sheet.setColumnWidth(j, titleLength[j]);}}}（5）新建⼀个Service接⼝；package com.twf.springcloud.ExportExcel.sevice;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.http.ResponseEntity;public interface ExportService {ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response);}（6）新建⼀个Service接⼝实现类；package com.twf.springcloud.ExportExcel.sevice.impl;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import ermodel.CellStyle;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.http.ResponseEntity;import org.springframework.stereotype.Service;import com.twf.springcloud.ExportExcel.controller.BaseFrontController;import er;import com.twf.springcloud.ExportExcel.sevice.ExportService;import com.twf.springcloud.ExportExcel.utils.ExcelFormatUtil;@Servicepublic class ExportServiceImpl implements ExportService{Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);@Overridepublic ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) { try {(">>>>>>>>>>开始导出excel>>>>>>>>>>");// 造⼏条数据List<User> list = new ArrayList<>();list.add(new User("唐三藏", "男", 30, "134********", "东⼟⼤唐", "取西经"));list.add(new User("孙悟空", "男", 29, "134********", "菩提院", "打妖怪"));list.add(new User("猪⼋戒", "男", 28, "134********", "⾼⽼庄", "偷懒"));list.add(new User("沙悟净", "男", 27, "134********", "流沙河", "挑担⼦"));BaseFrontController baseFrontController = new BaseFrontController();return baseFrontController.buildResponseEntity(export((List<User>) list), "⽤户表.xls"); } catch (Exception e) {e.printStackTrace();logger.error(">>>>>>>>>>导出excel 异常，原因为：" + e.getMessage());}return null;}private InputStream export(List<User> list) {(">>>>>>>>>>>>>>>>>>>>开始进⼊导出⽅法>>>>>>>>>>");ByteArrayOutputStream output = null;InputStream inputStream1 = null;SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中SXSSFSheet sheet = wb.createSheet();// 设置报表头样式CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式// 每⼀列字段名String[] strs = new String[] { "姓名", "性别", "年龄", "⼿机号", "地址","爱好" };// 字段名所在表格的宽度int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 };// 设置表头样式ExcelFormatUtil.initTitleEX(sheet, header, strs, ints);(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");if (list != null && list.size() > 0) {(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>"); for (int i = 0; i < list.size(); i++) {User user = list.get(i);SXSSFRow row = sheet.createRow(i + 1);int j = 0;SXSSFCell cell = row.createCell(j++);cell.setCellValue(user.getName()); // 姓名cell.setCellStyle(content);cell = row.createCell(j++);cell.setCellValue(user.getSex()); // 性别cell.setCellStyle(content);cell = row.createCell(j++);cell.setCellValue(user.getAge()); // 年龄cell.setCellStyle(content);cell = row.createCell(j++);cell.setCellValue(user.getPhoneNo()); // ⼿机号cell.setCellStyle(content);cell = row.createCell(j++);cell.setCellValue(user.getAddress()); // 地址cell.setCellStyle(content);cell = row.createCell(j++);cell.setCellValue(user.getHobby()); // 爱好cell.setCellStyle(content);}(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>"); }try {output = new ByteArrayOutputStream();wb.write(output);inputStream1 = new ByteArrayInputStream(output.toByteArray());output.flush();} catch (Exception e) {e.printStackTrace();} finally {try {if (output != null) {output.close();if (inputStream1 != null)inputStream1.close();}} catch (IOException e) {e.printStackTrace();}}return inputStream1;}}（7）新建⼀个下载⽂件的通⽤controller；package com.twf.springcloud.ExportExcel.controller;import java.io.InputStream;import .URLEncoder;import java.util.HashMap;import java.util.Map;import org.apache.poi.util.IOUtils;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.http.HttpHeaders;import org.springframework.http.HttpStatus;import org.springframework.http.ResponseEntity;import org.springframework.util.StringUtils;import org.springframework.validation.annotation.Validated;@Validatedpublic class BaseFrontController {/*** slf4j ⽇志 logger*/protected final Logger logger = LoggerFactory.getLogger(this.getClass());/*** 下载⽂件,纯SpringMVC的API来完成** @param is ⽂件输⼊流* @param name ⽂件名称,带后缀名** @throws Exception*/public ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws Exception {(">>>>>>>>>>>>>>>>>>>>开始下载⽂件>>>>>>>>>>");if (this.logger.isDebugEnabled())this.logger.debug("download: " + name);HttpHeaders header = new HttpHeaders();String fileSuffix = name.substring(stIndexOf('.') + 1);fileSuffix = fileSuffix.toLowerCase();Map<String, String> arguments = new HashMap<String, String>();arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");arguments.put("xls", "application/vnd.ms-excel");String contentType = arguments.get(fileSuffix);header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download"));if(is!=null && is.available()!=0){header.add("Content-Length", String.valueOf(is.available()));header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); byte[] bs = IOUtils.toByteArray(is);(">>>>>>>>>>>>>>>>>>>>结束下载⽂件-有记录>>>>>>>>>>");(">>>>>>>>>>结束导出excel>>>>>>>>>>");return new ResponseEntity<>(bs, header, HttpStatus.OK);}else{String string="数据为空";header.add("Content-Length", "0");header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); (">>>>>>>>>>>>>>>>>>>>结束下载⽂件-⽆记录>>>>>>>>>>");(">>>>>>>>>>结束导出excel>>>>>>>>>>");return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);}}}（8）新建⼀个controller，作为请求的⼊⼝；package com.twf.springcloud.ExportExcel.controller;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.http.ResponseEntity;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.twf.springcloud.ExportExcel.sevice.ExportService;@RestController@RequestMapping("/exportExcel/")public class ExportController {@Autowiredprivate ExportService exportService;// 导出excel@RequestMapping("exportExcel")public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) {return exportService.exportExcel(request,response);}}（10）以上就是本⽂的全部内容，希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。

java导出Excel⼤数据量，⾃⼰经验总结！出处：分析导出实现代码，XLSX⽀持：/*** ⽣成<span style="white-space: normal; background-color: #ffffff;">XLSX</span>,2007版本的excel,每个sheet⽆6.5W的⾏数限制，但是到了⼀定数量，可能内存溢出，* 次⽅法适合在预计10W以下的数据导出时使⽤,本机测试，14W可以导出。

列数量在8列左右** @param fileOut* 输出流* @param sheetMap* 要设置的数据信息* @throws SQLException*/public static void createXSLXByResultSet(OutputStream fileOut, WriteXLSBean... beans)throws SQLException {try {//重点 WorkbookWorkbook wb = new XSSFWorkbook();for (int i = 0, len = beans.length; i < len; i++) {WriteXLSBean xlsBean = beans[i];Sheet sheet = wb.createSheet(xlsBean.getSheetName());ResultSet rs = xlsBean.getRs();ResultSetMetaData rsmd = rs.getMetaData();TypeHandlerRegistry tr = BeanContext.tr;Map<String, String> th = xlsBean.getTh();int index = 0;while (rs.next()) {long t1 = System.currentTimeMillis();ermodel.Row row = sheet.createRow(index);for (int j = 0, numberOfColumns = rsmd.getColumnCount(); j < numberOfColumns; j++) {String key = rsmd.getColumnLabel(j + 1).toLowerCase();if (th.containsKey(key)) {TypeHandler<?> type = tr.getTypeHandler(JdbcType.forCode(rsmd.getColumnType(j + 1)));Object obj = type.getResult(rs, key);row.createCell(j).setCellValue(obj == null ? "": obj.toString());}}System.out.println(index + " :"+ (System.currentTimeMillis() - t1));index++;}}//重点 Workbookwb.write(fileOut);} catch (IOException e) {e.printStackTrace();throw new ServiceRunTimeException("⽣产xls⽂档错误", e);} finally {}}在上⾯标注了重点的两处，分别是：1.构建⼀个Excel对象2.将该对象写⼊⼀个OutPutStream⽽在构建过程中，没有地⽅写⼊OutPutSteam ,也就是说必须在内存中构建整个 Excel，才能进⾏写出操作，在⼤数据量情况下，这样将导致所有数据加载到内存中，⽽不能输出，导致最后内存溢出。