把数据库表导出到excel表

最新推荐文章于 2024-04-01 16:36:14 发布

钟俊辉

最新推荐文章于 2024-04-01 16:36:14 发布

阅读量297

点赞数

分类专栏： Java开发工程师全站文章标签：导出数据至Excel表

本文链接：https://blog.csdn.net/weixin_42187447/article/details/91558522

版权

Java开发工程师同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

全站

2 篇文章 0 订阅

订阅专栏

一个人完成了整个项目，正式成为全站了，分享一个小功能。
框架 springboot+Mybatis+pgsql
前端：HTML5+ thymeleaf
第一步：添加配置文件

	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
	   <groupId>org.apache.poi</groupId>
	   <artifactId>poi</artifactId>
	   <version>3.17</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	   <groupId>org.apache.poi</groupId>
	   <artifactId>poi-ooxml</artifactId>
	   <version>3.17</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
	<dependency>
	   <groupId>org.apache.poi</groupId>
	   <artifactId>poi-ooxml-schemas</artifactId>
	   <version>3.17</version>
	</dependency>
	<!--数据导出依赖 End excel-->

第二步：创建bean，封装对象

public class DemandColumnTitleMap {
	private Map<String, String> columnTitleMap = new HashMap<String, String>();
    private ArrayList<String> titleKeyList = new ArrayList<String> ();
    public DemandColumnTitleMap(String datatype) {
        switch (datatype) {
            case "userinfo":
                initUserInfoColu();
                initUserInfoTitleKeyList();
                break;
            default:
                break;
        }

    }
    
private void initUserInfoColu() {
	 columnTitleMap.put("requireid", "需求编号");
     columnTitleMap.put("postname", "提出据点");
     columnTitleMap.put("requirename", "需求名称");
    
}
private void initUserInfoTitleKeyList() {
	titleKeyList.add("requireid");
    titleKeyList.add("postname");
    titleKeyList.add("requirename");
  
}

public Map<String, String> getColumnTitleMap() {
    return columnTitleMap;
}

public ArrayList<String> getTitleKeyList() {
    return titleKeyList;
}

}

*第三步：编写SQL 重点（resultType="HashMap）

<select id="getDemandList" parameterType="com.zte.saltportal.model.demand.DemandQueryCriteria" resultType="HashMap">
select * from AA
</select>

第四步：dao方法封装

List<Map<String,Object>> getDemandList(DemandQueryCriteria bean);

第五步：ServiceImpl实现接口方法，

@Override
	public List<Map<String,Object>> getDemandLists(DemandQueryCriteria bean){
		return demandDao.getDemandLists(bean);
	}
**第六步：重点导出封装**
	（1）创建ExportDataService 


 @Service
    public class ExportDataService {
    	
	@Autowired
    ExportExcelUtil exportExcelUtil;
/*导出用户数据表*/
public void exportDataToEx(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) {
    try {
    	System.out.println("调用导出数据到Excel方法");
        exportExcelUtil.expoerDataExcel(response, titleKeyList, titleMap, src_list);
        System.out.println("调用导出数据到Excel方法结束");
    } catch (Exception e) {
        System.out.println("Exception: " + e.toString());
    }
}
}

（2）创建ExportExcelUtil

@Service
public class ExportExcelUtil {
	public void expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) throws IOException {
		
	//这里为导出文件存放的路径	
	String filePath ="C:\\Users\\USER" + UUID.randomUUID() + "\\";
	//加入一个uuid随机数是因为	
	//每次导出的时候，如果文件存在了，会将其覆盖掉，这里是保存所有的文件	
	File file = new File(filePath);	
	if (!file.exists()) {		
			file.mkdirs();	
	}
	SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");	
    String xlsFile_name = filePath + "DEMAND" + "_" + fmt.format(new Date()) + ".xlsx";     //输出xls文件名称
    System.out.println("Excel表存储路径："+xlsFile_name);
    //内存中只创建100个对象
    Workbook wb = new SXSSFWorkbook(1000000000);           //关键语句
    Sheet sheet = null;     //工作表对象
    Row nRow = null;        //行对象
    Cell nCell = null;      //列对象

    int rowNo = 0;      //总行号
    int pageRowNo = 0;  //页行号

    for (int k=0;k<src_list.size();k++) {
        Map<String,Object> srcMap = src_list.get(k);
        //写入300000条后切换到下个工作表
        if(rowNo%300000==0){
            System.out.println("Current Sheet:" + rowNo/300000);
            sheet = wb.createSheet("工作簿"+(rowNo/300000));//建立新的sheet对象
            sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
            pageRowNo = 0;      //新建了工作表,重置工作表的行号为0
            // -----------定义表头-----------
            nRow = sheet.createRow(pageRowNo++);
            // 列数 titleKeyList.size()
            for(int i=0;i<titleKeyList.size();i++){
                Cell cell_tem = nRow.createCell(i);
                cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
            }
            rowNo++;
            // ---------------------------
        }
        rowNo++;
        nRow = sheet.createRow(pageRowNo++);    //新建行对象

        // 行，获取cell值
        for(int j=0;j<titleKeyList.size();j++){
            nCell = nRow.createCell(j);
            if (srcMap.get(titleKeyList.get(j)) != null) {
                nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
            } else {
                nCell.setCellValue("");
            }
        }
    }
   try {
	   response.setContentType("application/vnd.ms-excel;charset=utf-8");
       response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
       response.flushBuffer();
       OutputStream outputStream = response.getOutputStream();
       wb.write(response.getOutputStream());
       wb.close();
       outputStream.flush();
       outputStream.close();
   } catch (Exception e) {
	   System.out.println(e.toString());
	   e.printStackTrace();
   }   
    }
 }

第七步：HTML页面请求

导出按钮

<button type="button" class="btn btn-info iptv-plus" onclick="doadd()">导出</button>

发送客户端请求，*确保能够导出，我选择最简单的请求路径

 <script  th:inline="javascript">
 function doadd(){
	window.location.href="/saltportal/demand/doadd";
}
 </script>

第八步：服务端进行数据处理
【*】由于我导出的数据中时间需要处理，由20190101导入报表后为2019/01/01

@ResponseBody
	  @RequestMapping(value="/demand/doadd")
	  public void doadd(HttpServletResponse response,DemandQueryCriteria bean){
		  try {
			  List<Map<String,Object>> list = demandService.getDemandLists(bean);
			  for(Map<String,Object> map: list){
				 	 if(map.get("starttime")!=null){
					 String starttime = map.get("starttime").toString();
						 if(starttime.length()>0){
						 	String starttimes = Conversion(starttime);
						 	map.put("starttime", starttimes);
						 }
				 	 }
 				}
			  System.out.println(list.size());
			  ArrayList<String> titleKeyList= new DemandColumnTitleMap("userinfo").getTitleKeyList();
			  Map<String, String> titleMap = new DemandColumnTitleMap("userinfo").getColumnTitleMap();
			  exportDataService.exportDataToEx(response, titleKeyList, titleMap, list);
		  } catch (Exception e) {
			  System.out.println(e.toString());
			  e.printStackTrace();
		  }
	  }
	  
	  //把时间格式20190512转成2019/05/12
	  public String Conversion(String time){
		  StringBuilder  sb=new  StringBuilder (time);
		  	String sb1 = "";
		  	sb.insert(4,"/");
			sb.insert(7,"/");
			sb1 = sb.toString();
			 return sb1;
	  }

导出功能就结束了，希望能帮助到你！