文章詳情頁

Java操作Excel、PDF文件(2)

瀏覽：115日期：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()+"的選課學生列表"// 標題

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中NORMSINV函數使用教程分享2. excel按回車鍵沒反應解決方法分享3. 處理Excel插入按鈕是灰色的方法4. excel添加書名號方法介紹5. Win10系統Excel打開很慢怎么辦？6. Excel中如何使選中的單元格變色7. excel取消固定格式設置8. Excel如何打印部分表格？Excel打印部分表格教程9. excel單元格轉置步驟介紹10. Excel插入分頁符的方法和步驟

排行榜

					
					今日頭條極速版簽到在哪里
輕顏相機內存怎么清理
qq群怎么修改群頭像
一罐怎么刪掉聊天記錄？刪掉聊天記錄的方法說明
網易云音樂app設置個人主頁信息的方法
qq閃照在哪個文件夾
微信歡樂球球控制不了怎么回事 微信歡樂球球動不了怎么辦
qq畫圖紅包籃子怎么畫？QQ畫圖紅包所有圖案畫法匯總
excel中NORMSINV函數使用教程分享
ps分辨率一般是多少詳情
微信朋友圈帶刪除線的句子怎么發 朋友圈字體中間加一條橫線怎么弄
				