文章详情页

Java操作Excel、PDF文件(2)

更多 QQ空间微信 QQ好友腾讯朋友复制链接

【字号：大中小】日期：2022-07-21 16:19:46浏览：73作者：猪猪

wbook.write(); // 写入文件

wbook.close();

os.close();

return "success";

}

public class ExcelBean {

public String expordExcel(OutputStream os, List courseList,List studentList)

throws Exception {

WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件

String tmptitle = "课程“"+((Course_info)courseList.get(0)).getCource_name()+"的选课学生列表"// 标题

WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称

// 设置excel标题

WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,

WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,

Colour.BLACK);

WritableCellFormat wcfFC = new WritableCellFormat(wfont);

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, "学号"));

.........

for(int i=3;i<studentList.size()+3;i++)

{

wsheet.addCell(new Label(0, i, ((Course_info)courseList.get(0)).getCource_name()));

wsheet.addCell(new Label(1, i, ((Student_info)studentList.get(0)).getStudentID()));

...........

}

// 主体内容生成结束

wbook.write(); // 写入文件

wbook.close();

os.close();

return "success";

}

控制器：

Java代码

public class EExcelDownController extends AbstractController {

private ICourse_infoManage courseManage;

public void setCourseManage(ICourse_infoManage courseManage) {

this.courseManage = courseManage;

}

@Override

protected ModelAndView handleRequestInternal(HttpServletRequest request,

HttpServletResponse response) throws Exception {

Integer course_id=new Integer(request.getParameter("course_id"));

List courseList=this.courseManage.getCourseById(course_id);

List studentList = this.courseManage.getStudentByCourseId(course_id);

try {

OutputStream os = response.getOutputStream();// 取得输出流

response.reset();// 清空输出流

response.setHeader("Content-disposition", "attachment; filename=student.xls");// 设定输出文件头

response.setContentType("application/msexcel");// 定义输出类型

ExcelBean excelBean = new ExcelBean();

excelBean.expordExcel(os,courseList,studentList);// 调用生成excel文件bean

} catch (Exception e) {

System.out.println(e);

}

return null;

}

将Excel文件内容写入到数据库

Java代码

publicclass EStudentInsertExcelController extends SimpleFormController{

private IStudent_infoManage studentManage;

@Override

protected ModelAndView onSubmit(HttpServletRequest request,

HttpServletResponse response, Object command, BindException errors)

throws Exception{

Student_info student_info = (Student_info) command;

try;{

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

MultipartFile file = multipartRequest.getFile( " Excelfile " ); // 获得文件：

File toFile =new File( " c:学生信息临时文件.xls " ); // 产生文件名和空文件

file.transferTo(toFile); // 文件上传

Workbook book = Workbook.getWorkbook(toFile); // 得到工作薄

Sheet sheet = book.getSheet( 0 ); // 获得第一个工作表对象

int row = sheet.getRows(); // /得到该sheet的行数

int column = sheet.getColumns(); // 得到该sheet的列数

System.out.println( " 数据行数= " + row);

System.out.println( " 数据列数= " + column);

for ( int i = 1 ;i < row;i ++ )

{

for ( int j = 0 ;j < column;j ++ )

{

System.out.println( " j= " + j);

sheet.getCell(j, i).getContents(); // 得到第j列第i行的单元格的类容

student_info.setStudentID(sheet.getCell(j, i).getContents());

........................

}

if ( this .studentManage.getStudentByStudentID(

student_info.getStudentID()).size() !=0 )

returnnew ModelAndView( " education/e-studentInfoAddError " );

this .studentManage.insertStudent_info(student_info);

}

book.close();

returnnew ModelAndView( " education/e-studentInfoAddExcelSuccess " , " row " , new Integer(row - 1 ));

}catch (Exception e){

e.printStackTrace();

}

returnnew ModelAndView( " education/e-studentInfoAddExcelError " );

}

publicvoid setStudentManage(IStudent_infoManage studentManage){

this .studentManage = studentManage;

}

spring 生成Excel和PDF文件

HTML页面并不总是向用户显示数据输出的最好方式，有时候需要生成不可改变的文件打印，PDF可能是种不错的选择。

Spring支持从数据动态生成PDF或Excel文件

下面这个简单实现的例子实现了spring输出PDF和Excel文件，为了使用Excel电子表格，你需要在你的classpath中加入poi-2.5.1.jar库文件，而对PDF文件，则需要iText.jar文件。它们都包含在Spring的主发布包中。

下面是测试项目代码：

1、控制器配置代码

Java代码

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

<bean id="beanNameViewResolver"

class="org.springframework.web.servlet.view.BeanNameViewResolver" />

<bean id="urlMapping"

class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">

<props>

<prop key="/view*.shtml">viewController</prop>

</props>

</property>

</bean>

</beans>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

<bean id="beanNameViewResolver"

class="org.springframework.web.servlet.view.BeanNameViewResolver" />

<bean id="urlMapping"

class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">

<props>

<prop key="/view*.shtml">viewController</prop>

</props>

</property>

</bean>

</beans>

3、用于Excel视图的视图子类化

为了在生成输出文档的过程中实现定制的行为，我们将继承合适的抽象类。对于Excel，这包括提供一个 org.springframework.web.servlet.view.document.AbstractExcelView的子类，并实现 buildExcelDocument方法。

Java代码

public class ViewExcel extends AbstractExcelView {

public void buildExcelDocument(

Map model, HSSFWorkbook workbook,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

HSSFSheet sheet = workbook.createSheet("list");

sheet.setDefaultColumnWidth((short) 12);

HSSFCell cell = getCell(sheet, 0, 0);

setText(cell, "Spring Excel test");

HSSFCellStyle dateStyle = workbook.createCellStyle();

dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

cell = getCell(sheet, 1, 0);

cell.setCellValue(new Date());

cell.setCellStyle(dateStyle);

getCell(sheet, 2, 0).setCellValue(458);

HSSFRow sheetRow = sheet.createRow(3);

for (short i = 0; i < 10; i++) {

sheetRow.createCell(i).setCellValue(i * 10);

}

public class ViewExcel extends AbstractExcelView {

public void buildExcelDocument(

Map model, HSSFWorkbook workbook,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

HSSFSheet sheet = workbook.createSheet("list");

sheet.setDefaultColumnWidth((short) 12);

HSSFCell cell = getCell(sheet, 0, 0);

setText(cell, "Spring Excel test");

HSSFCellStyle dateStyle = workbook.createCellStyle();

dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

cell = getCell(sheet, 1, 0);

cell.setCellValue(new Date());

cell.setCellStyle(dateStyle);

getCell(sheet, 2, 0).setCellValue(458);

HSSFRow sheetRow = sheet.createRow(3);

for (short i = 0; i < 10; i++) {

sheetRow.createCell(i).setCellValue(i * 10);

}

4、用于PDF视图的视图子类化

需要象下面一样继承org.springframework.web.servlet.view.document.AbstractPdfView，并实现buildPdfDocument()方法。

Java代码

public class ViewPDF extends AbstractPdfView {

public void buildPdfDocument(Map model, Document document,

PdfWriter writer, HttpServletRequest request,

HttpServletResponse response) throws Exception {

List list = (List) model.get("list");

for (int i = 0; i < list.size(); i++)

document.add(new Paragraph((String) list.get(i)));

}

public class ViewPDF extends AbstractPdfView {

public void buildPdfDocument(Map model, Document document,

PdfWriter writer, HttpServletRequest request,

HttpServletResponse response) throws Exception {

List list = (List) model.get("list");

for (int i = 0; i < list.size(); i++)

document.add(new Paragraph((String) list.get(i)));

}

5、其他文件

1）控制器ViewController

Java代码

public class ViewController extends MultiActionController{

public ModelAndView viewPDF(HttpServletRequest request, HttpServletResponse response) throws Exception {

List list = new ArrayList();

Map model=new HashMap();

list.add("test1");

list.add("test2");

model.put("list",list);

ViewPDF viewPDF=new ViewPDF();

return new ModelAndView(viewPDF,model);

}

public ModelAndView viewExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {

List list = new ArrayList();

Map model=new HashMap();

list.add("test1");

list.add("test2");

model.put("list",list);

ViewExcel viewExcel=new ViewExcel();

return new ModelAndView(viewExcel,model);

}

public class ViewController extends MultiActionController{

public ModelAndView viewPDF(HttpServletRequest request, HttpServletResponse response) throws Exception {

List list = new ArrayList();

Map model=new HashMap();

list.add("test1");

list.add("test2");

model.put("list",list);

ViewPDF viewPDF=new ViewPDF();

return new ModelAndView(viewPDF,model);

}

public ModelAndView viewExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {

List list = new ArrayList();

Map model=new HashMap();

list.add("test1");

list.add("test2");

model.put("list",list);

ViewExcel viewExcel=new ViewExcel();

return new ModelAndView(viewExcel,model);

}

2）web.xml

Java代码

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<display-name>springPDFTest</display-name>

<servlet-name>springPDFTest</servlet-name>

<servlet-class>

org.springframework.web.servlet.DispatcherServlet

</servlet-class>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>springPDFTest</servlet-name>

<url-pattern>*.shtml</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>index.jsp</welcome-file>

</welcome-file-list>

</web-app>

<?xml version="1.0" encoding="UTF-8"?>

<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<display-name>springPDFTest</display-name>

<servlet-name>springPDFTest</servlet-name>

<servlet-class>

org.springframework.web.servlet.DispatcherServlet

</servlet-class>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>springPDFTest</servlet-name>

<url-pattern>*.shtml</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>index.jsp</welcome-file>

</welcome-file-list>

</web-app>

3)index.jsp

Java代码

<%@ page contentType="text/html; charset=gb2312"%>

<a href="viewPDF.shtml">PDF视图打开 </a>

<br>

<a href="viewExcel.shtml">Excel视图打开</a>

网页链接到Excel打开时乱码解决办法：

问题：

Java代码

< ahref ="excel/test.xls" > 打开 </ a > 【乱码】

解决方法：

web.xml里加上：

Java代码

< mime-mapping >

< extension > xls </ extension >

< mime-type > application/vnd.ms-excel </ mime-type >

</ mime-mapping >

< mime-mapping >

< extension > xls </ extension >

< mime-type > application/vnd.ms-excel </ mime-type >

</ mime-mapping >;

excel

上一条：如何把支付宝芝麻信用分提高到800？支付宝芝麻信用分提高到800的方法下一条：Java操作Excel、PDF文件(1)

相关文章：

1. EXCEL单元格内容对齐方式2. JAVA如何读取Excel数据3. excel查找重复项4. excel2019怎么删除工作表行5. excel怎么按日期进行数据筛选？6. WPS中Excel怎么提取数据的众数7. 使用IDEA创建java项目的步骤详解（hello word）8. WPS中Excel怎么数据筛选和排序9. Java微信授权登陆的实现示例10. Excel表格如何使用行列拼接功能？方方格子工具教你快速使用行列拼接

排行榜

					
					上海公交卡怎么绑定在手机上
滴滴优享订单怎么用
支付宝沾福气卡怎么用  支付宝沾福气卡使用方法介绍
中国锦鲤2019年还有吗？支付宝中国锦鲤2019年开奖详情
百度旅游怎么查看足迹地图？详细操作流程
知乎怎么加通讯录好友
QQ游戏大厅怎么添加好友？添加好友方式详解
Boss直聘怎么发布招聘信息？Boss直聘发布招聘信息的方法
快手查订单怎么查
百度手机助手PC版连接手机具体操作步骤
qq浏览器怎么开启兼容模式？qq浏览器开启兼容模式的方法
				

热门标签