search
HomeJavajavaTutorialJava 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

Java realizes the transfer of data queried from the database into an excel table

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!

Statement
This article is reproduced at:CSDN. If there is any infringement, please contact admin@php.cn delete
How do I use Maven or Gradle for advanced Java project management, build automation, and dependency resolution?How do I use Maven or Gradle for advanced Java project management, build automation, and dependency resolution?Mar 17, 2025 pm 05:46 PM

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

How do I create and use custom Java libraries (JAR files) with proper versioning and dependency management?How do I create and use custom Java libraries (JAR files) with proper versioning and dependency management?Mar 17, 2025 pm 05:45 PM

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

How do I implement multi-level caching in Java applications using libraries like Caffeine or Guava Cache?How do I implement multi-level caching in Java applications using libraries like Caffeine or Guava Cache?Mar 17, 2025 pm 05:44 PM

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

How can I use JPA (Java Persistence API) for object-relational mapping with advanced features like caching and lazy loading?How can I use JPA (Java Persistence API) for object-relational mapping with advanced features like caching and lazy loading?Mar 17, 2025 pm 05:43 PM

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]

How does Java's classloading mechanism work, including different classloaders and their delegation models?How does Java's classloading mechanism work, including different classloaders and their delegation models?Mar 17, 2025 pm 05:35 PM

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

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

DVWA

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

SublimeText3 Chinese version

Chinese version, very easy to use

mPDF

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

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function