java使用poi导出数据成excel

最新推荐文章于 2023-01-31 15:10:35 发布

国服第一打野

最新推荐文章于 2023-01-31 15:10:35 发布

阅读量180

点赞数

分类专栏： java后端

本文链接：https://blog.youkuaiyun.com/weixin_41618475/article/details/103361962

版权

java后端专栏收录该内容

13 篇文章

订阅专栏

个人笔记。

前台页面

导出的excel

代码如下

jsp

<%@ page contentType="text/html;charset=utf8"%>
<html>
<body>
	<table border="1">
		<tr>
			<th>姓名</th>
			<th>年龄</th>
			<th>性别</th>
		</tr>
		<tr>
			<td>张三</td>
			<td>18</td>
			<td>男</td>
		</tr>
		<tr>
			<td>李四</td>
			<td>18</td>
			<td>女</td>
		</tr>
	</table>
	<button id="downloadExcelBtn">导出Excel</button>
</body>
<script src="/Demo/js/jquery.min.js"></script>
<script type="text/javascript">
	$(function() {
		$("#downloadExcelBtn").click(function () {
			$.get("/Demo/exportFile.do","",function (data) {
				if (data == "SUCCESS") {
					alert('成功');
				}
			})
		})
	})
</script>
</html>

controller

package com.sh.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.sh.entity.Student;
@Controller
public class FileController {
	
	@RequestMapping("/hello.do")
	public String hello(){
		return "index";
	}
	
	@RequestMapping("/exportFile.do")
	@ResponseBody
	public String exportFile(HttpServletRequest request,
			HttpServletResponse response){
		try {
			// 造一些假数据,一般都是直接从数据库中查出来的
			List<Student> sList = new ArrayList<Student>();
			Student sd = new Student("张三","18","男");
			Student sd2 = new Student("李四","18","女");
			sList.add(sd);
			sList.add(sd2);
			
			// 保存的文件名
			String fileName = "StudentInfo.xls";
			// 保存路径
			String filePath = "/cpic/excel/";
			
			response.setHeader("Content-type", "text/html;charset=UTF-8");
			ExportStudentExcel("Student",sList,fileName,filePath);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "SUCCESS";
	}

	private void ExportStudentExcel(String title,List<Student> sList, String fileName,
			String filePath) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();//Excel的文档对象
		HSSFSheet sheet = wb.createSheet(title);//创建一个sheet页
		HSSFRow row = sheet.createRow((int) 0);//创建一行
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		
		HSSFCell cell = row.createCell(0);//Excel的格子单元
		
		cell.setCellValue("姓名");
		cell.setCellStyle(style);
		cell = row.createCell(1);
		
		cell.setCellValue("年龄");
		cell.setCellStyle(style);
		cell = row.createCell(2);
		
		cell.setCellValue("性别");
		cell.setCellStyle(style);
		cell = row.createCell(3);
		
		for (int i = 0; i < sList.size(); i++) {
			Student student = sList.get(i);
			row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(student.getName());
			row.createCell(1).setCellValue(student.getAge());
			row.createCell(2).setCellValue(student.getSex());
		}
		
		sheet.autoSizeColumn((short)0);
		sheet.setColumnWidth((short)1, (short)(18*256+184));
		sheet.setColumnWidth((short)8, (short)(18*256+184));
		sheet.setColumnWidth((short)9, (short)(18*256+184));
		
		File file = new File(filePath);
		if (!file.exists()) {
			file.mkdirs();
		}
		FileOutputStream fos = new FileOutputStream(filePath + File.separator + fileName);
		wb.write(fos);
		fos.close();
	}
}

entity

package com.sh.entity;

public class Student {
	
	public Student(String name,String age,String sex){
		this.name = name;
		this.age = age;
		this.sex = sex;
	}
	private String name;
	private String age;
	private String sex;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	
}

web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
	<display-name>Spring MVC Application</display-name>

	<!-- 解决springMVC的post乱码 -->
	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
		<init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

	<!-- springMVC的核心控制器 -->
	<servlet>
		<servlet-name>springMVC</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath*:spring-mvc.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>springMVC</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>
</web-app>

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>cn.sz</groupId>
	<artifactId>Demo</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>Demo Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
		<!-- spring-mvc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>5.2.0.RELEASE</version>
		</dependency>
		<!--读取excel文件 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>Demo</finalName>
	</build>
</project>

spring-mvc.xml

<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:context="http://www.springframework.org/schema/context"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:mvc="http://www.springframework.org/schema/mvc"
   xsi:schemaLocation="
   http://www.springframework.org/schema/beans     
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/context 
   http://www.springframework.org/schema/context/spring-context-3.0.xsd
   http://www.springframework.org/schema/mvc
   http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

   <context:component-scan base-package="com.sh.controller" />

	<!-- 视图解析器 -->
   <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
      <property name="prefix" value="/WEB-INF/jsp/" />
      <property name="suffix" value=".jsp" />
   </bean>
   
</beans>