文章詳情頁(yè)

Java操作Excel、PDF文件(2)

瀏覽：149日期：2022-07-21 16:19:46

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()+"的選課學(xué)生列表"// 標(biāo)題

WritableSheet wsheet = wbook.createSheet("第一頁(yè)", 0); // sheet名稱

// 設(shè)置excel標(biāo)題

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);

// 開始生成主體內(nèi)容

wsheet.addCell(new Label(0, 2, "課程名稱"));

wsheet.addCell(new Label(1, 2, "學(xué) 號(hào)"));

.........

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()));

...........

}

// 主體內(nèi)容生成結(jié)束

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");// 設(shè)定輸出文件頭

response.setContentType("application/msexcel");// 定義輸出類型

ExcelBean excelBean = new ExcelBean();

excelBean.expordExcel(os,courseList,studentList);// 調(diào)用生成excel文件bean

} catch (Exception e) {

System.out.println(e);

}

return null;

}

將Excel文件內(nèi)容寫入到數(shù)據(jù)庫(kù)

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:學(xué)生信息臨時(shí)文件.xls " ); // 產(chǎn)生文件名和空文件

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

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

Sheet sheet = book.getSheet( 0 ); // 獲得第一個(gè)工作表對(duì)象

int row = sheet.getRows(); // /得到該sheet的行數(shù)

int column = sheet.getColumns(); // 得到該sheet的列數(shù)

System.out.println( " 數(shù)據(jù)行數(shù)= " + row);

System.out.println( " 數(shù)據(jù)列數(shù)= " + 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頁(yè)面并不總是向用戶顯示數(shù)據(jù)輸出的最好方式，有時(shí)候需要生成不可改變的文件打印，PDF可能是種不錯(cuò)的選擇。

Spring支持從數(shù)據(jù)動(dòng)態(tài)生成PDF或Excel文件

下面這個(gè)簡(jiǎn)單實(shí)現(xiàn)的例子實(shí)現(xiàn)了spring輸出PDF和Excel文件，為了使用Excel電子表格，你需要在你的classpath中加入poi-2.5.1.jar庫(kù)文件，而對(duì)PDF文件，則需要iText.jar文件。它們都包含在Spring的主發(fā)布包中。

下面是測(cè)試項(xiàng)目代碼：

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視圖的視圖子類化

為了在生成輸出文檔的過(guò)程中實(shí)現(xiàn)定制的行為，我們將繼承合適的抽象類。對(duì)于Excel，這包括提供一個(gè) org.springframework.web.servlet.view.document.AbstractExcelView的子類，并實(shí)現(xiàn) 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，并實(shí)現(xiàn)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>

網(wǎng)頁(yè)鏈接到Excel打開時(shí)亂碼解決辦法：

問(wèn)題：

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)

相關(guān)文章：

1. excel怎么換順序? excel換順序方法一覽2. excel2019創(chuàng)建工作簿的方法3. win7系統(tǒng)中excel導(dǎo)入外部數(shù)據(jù)出錯(cuò)的解決方法4. excel中豎式除號(hào)制作教程分享5. Excel只打印部分內(nèi)容怎么配置？Excel不打印全部?jī)?nèi)容的方法6. EXCEL2019使用快速填充自動(dòng)填充列7. 用excel完成分?jǐn)?shù)ABCD評(píng)價(jià)方法分享8. excel怎么設(shè)置圖形樣式？9. Excel設(shè)置行高的兩種操作方式10. Excel如何制作自動(dòng)流水賬？

排行榜

					
					釘釘如何查看賬單？釘釘賬單查詢教程
支付寶五福打年獸必得紅包最大多少？教你快速打年獸得高分
抖音7+2=9是什么意思？抖音7加2等于9畫老鼠步驟方法介紹
豆瓣APP如何取消提醒 豆瓣關(guān)閉推送教程
抖音怎么查看福袋中獎(jiǎng)記錄
企業(yè)微信實(shí)名認(rèn)證在哪里
支付寶花唄支持哪些實(shí)體店鋪 支付寶螞蟻花唄可以掃一掃付款嗎
人人視頻app服務(wù)器異常
抖音如何清屏
【抖音處對(duì)象嗎GIF表情包】可愛酚酞瓜表情包分享
微信瘋狂猜猜第520關(guān)答案是什么？第520關(guān)答案分享
				