


Java realizes the transfer of data queried from the database into an excel table
After reading a lot of messy articles, I wrote a simple and violent one that I can understand at a glance. There are not so many bells and whistles. The table style can be defined through code. I find it troublesome
Note that if the date format is saved to the database in String type, it needs to be converted once. The direct export format is incorrect.
Because the excel table is exported using the get method to pass parameters, so if you need to export the data Use Chinese fuzzy query. At this time, when using get to pass parameters, Chinese garbled characters will appear
Solution:
The front end encodes the Chinese parameters that need to be passed URLEncoder.encode( Passing parameters, "utf-8");
The background needs to be decoded again: URLDecoder.decode(received parameters, "utf-8");
@RequestMapping(value = "outPutExcel", method = RequestMethod.GET) @ResponseBody public void outPutExcel( HttpServletResponse response,String officeid, String sonid,String nameorphone,String beginTime, String endTime,String option) { String nString = ""; try { if (nameorphone != null && nameorphone != "") { //对前端传的参数解码 nString = URLDecoder.decode(nameorphone,"UTF-8"); } } catch (UnsupportedEncodingException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } response.reset(); //设置浏览器下载的格式,并以当前时间的毫秒数命名 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); response.setContentType("application/msexcel"); List<PurchaseSum> list = purchaseService.selectPCSum(officeid, sonid, nString, beginTime, endTime, option); if (list == null && list.isEmpty()) { throw new NullPointerException("导出数据源为空"); } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet0"); HSSFRow rows; HSSFCell cells; //设置表格第一行的列名 // 获得表格第一行 rows = sheet.createRow(0); // 根据需要给第一行每一列设置标题 cells = rows.createCell(0); cells.setCellValue("客户姓名"); cells = rows.createCell(1); cells.setCellValue("客户电话"); cells = rows.createCell(2); cells.setCellValue("下单日期"); cells = rows.createCell(3); cells.setCellValue("订单号"); cells = rows.createCell(4); cells.setCellValue("所属分公司"); cells = rows.createCell(5); cells.setCellValue("签单人"); cells = rows.createCell(6); cells.setCellValue("品名"); cells = rows.createCell(7); cells.setCellValue("型号"); cells = rows.createCell(8); cells.setCellValue("颜色"); cells = rows.createCell(9); cells.setCellValue("尺寸"); cells = rows.createCell(10); cells.setCellValue("材质"); cells = rows.createCell(11); cells.setCellValue("已采购数量(件)"); cells = rows.createCell(12); cells.setCellValue("采购单价"); cells = rows.createCell(13); cells.setCellValue("采购总价"); cells = rows.createCell(14); cells.setCellValue("已出库(件)"); //循环数据库查出来的数据集,对应每一列赋值 //此处list.size()本不应该-1,因为同事在list集合里追加了另一条数据,导致报错故将其去除 for (int i = 0; i < list.size()-1; i++) { rows = sheet.createRow(i + 1); cells = rows.createCell(0); cells.setCellValue(list.get(i).getCustomerName()); cells = rows.createCell(1); cells.setCellValue(list.get(i).getPhone()); //对日期格式进行转换 cells = rows.createCell(2); String dateString = list.get(i).getPlaceOrderTime().toString(); Date date = null; try { date = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK).parse(dateString); } catch (ParseException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); cells.setCellValue(sdf.format(date)); cells = rows.createCell(3); cells.setCellValue(list.get(i).getOrderNumber()); cells = rows.createCell(4); cells.setCellValue(list.get(i).getOfficeName()); cells = rows.createCell(5); cells.setCellValue(list.get(i).getUsername()); cells = rows.createCell(6); cells.setCellValue(list.get(i).getProductName()); cells = rows.createCell(7); cells.setCellValue(list.get(i).getType()); cells = rows.createCell(8); cells.setCellValue(list.get(i).getColor()); cells = rows.createCell(9); cells.setCellValue(list.get(i).getSize()); cells = rows.createCell(10); cells.setCellValue(list.get(i).getTexture()); cells = rows.createCell(11); cells.setCellValue(list.get(i).getPurchasedNumber()); cells = rows.createCell(12); cells.setCellValue(list.get(i).getPurchaseprice()); cells = rows.createCell(13); cells.setCellValue(list.get(i).getPurchasePriceSun()); cells = rows.createCell(14); cells.setCellValue(list.get(i).getOutlibraryNumber()); } try { OutputStream oStream = response.getOutputStream(); wb.write(oStream); oStream.flush(); } catch (FileNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
The above is the detailed content of Java realizes the transfer of data queried from the database into an excel table. For more information, please follow other related articles on the PHP Chinese website!

The article discusses using Maven and Gradle for Java project management, build automation, and dependency resolution, comparing their approaches and optimization strategies.

The article discusses creating and using custom Java libraries (JAR files) with proper versioning and dependency management, using tools like Maven and Gradle.

The article discusses implementing multi-level caching in Java using Caffeine and Guava Cache to enhance application performance. It covers setup, integration, and performance benefits, along with configuration and eviction policy management best pra

The article discusses using JPA for object-relational mapping with advanced features like caching and lazy loading. It covers setup, entity mapping, and best practices for optimizing performance while highlighting potential pitfalls.[159 characters]

Java's classloading involves loading, linking, and initializing classes using a hierarchical system with Bootstrap, Extension, and Application classloaders. The parent delegation model ensures core classes are loaded first, affecting custom class loa


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 Chinese version
Chinese version, very easy to use

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function