Excel转Html - 王朝网络宽屏版

项目结构：

这是一个maven项目，主函数在Client类里面

当运行程序的后，控制台情况：

当我们刷新了test.html文件后，用浏览器打开效果：

说一下这个过程的设计思路：

1.读取Excel文件

2.利用velocity模板工具把读取的内容渲染到html里面

整个过程就两个步骤，是不是非常简单。

当我们在把这两个过程再细化一下，思路就更加清晰明了了。

1.1.怎样读取或者写入Excel文件呢？

java的poi技术读，写Excel[2003-2007,2010]

2.1.怎样使用velocity模板工具呢？

apache的开源项目-模板引擎(Velocity)_学习了两天就上手啦_源码下载

有了上面1.1和2.1的基础，现在我们要做的工作，就是把他们串起来，就实现了Excel转Html

为了自己以后一看源码就知道怎样做，我习惯贴源码出来。当然还会有源码下载的（在文章末尾）。

===============================================

源码部分：

===============================================

/excel2html/src/main/java/com/b510/excel/client/Client.java

1packagecom.b510.excel.client;23importjava.util.List;45importcom.b510.excel.common.Common;6importcom.b510.excel.reader.ReadExcel;7importcom.b510.excel.vo.Student;8importcom.b510.excel.writer.WriteHtml;910publicclassClient {1112publicstaticvoidmain(String[] args)throwsException {13String excel2010 =Common.STUDENT_INFO_XLSX_PATH;14//read the 2010 excel15List<Student> list1 =newReadExcel().readExcel(excel2010);16if(list1 !=null&& list1.size() > 0) {17for(Student student : list1) {18System.out.PRintln("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " +student.getScore());19}20System.out.println("begin to write into html file");21WriteHtml.write(list1);22}2324}25}

/excel2html/src/main/java/com/b510/excel/common/Common.java

1packagecom.b510.excel.common;23publicclassCommon {45publicstaticfinalString OFFICE_EXCEL_2010_POSTFIX = "xlsx";67publicstaticfinalString EMPTY = "";8publicstaticfinalString POINT = ".";9publicstaticfinalString STUDENT_INFO_XLSX_PATH = "/student_info" + POINT +OFFICE_EXCEL_2010_POSTFIX;10publicstaticfinalString NOT_EXCEL_FILE = " : Not the Excel file!";11publicstaticfinalString PROCESSING = "Processing...";1213publicstaticfinalString HTML_FILE = "test.html";14publicstaticfinalString TEST_HTML_FILE = "./test.html";1516}

/excel2html/src/main/java/com/b510/excel/reader/ReadExcel.java

1packagecom.b510.excel.reader;23importjava.io.IOException;4importjava.io.InputStream;5importjava.util.ArrayList;6importjava.util.List;78importorg.apache.poi.hssf.usermodel.HSSFCell;9importorg.apache.poi.hssf.usermodel.HSSFRow;10importorg.apache.poi.hssf.usermodel.HSSFSheet;11importorg.apache.poi.hssf.usermodel.HSSFWorkbook;12importorg.apache.poi.xssf.usermodel.XSSFCell;13importorg.apache.poi.xssf.usermodel.XSSFRow;14importorg.apache.poi.xssf.usermodel.XSSFSheet;15importorg.apache.poi.xssf.usermodel.XSSFWorkbook;1617importcom.b510.excel.common.Common;18importcom.b510.excel.util.Util;19importcom.b510.excel.vo.Student;2021publicclassReadExcel {2223/**24* read the Excel file25*@parampath the path of the Excel file26*@return27*@throwsIOException28*/29publicList<Student> readExcel(String path)throwsIOException {30if(path ==null||Common.EMPTY.equals(path)) {31returnnull;32}else{33String postfix =Util.getPostfix(path);34if(!Common.EMPTY.equals(postfix)) {35if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {36returnreadXlsx(path);37}38}else{39System.out.println(path +Common.NOT_EXCEL_FILE);40}41}42returnnull;43}4445/**46* Read the Excel 201047*@parampath the path of the excel file48*@return49*@throwsIOException50*/51@SuppressWarnings("resource")52publicList<Student> readXlsx(String path)throwsIOException {53System.out.println(Common.PROCESSING +path);54InputStream is =this.getClass().getResourceAsStream(path);55XSSFWorkbook xssfWorkbook =newXSSFWorkbook(is);56Student student =null;57List<Student> list =newArrayList<Student>();58//Read the Sheet59for(intnumSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {60XSSFSheet xssfSheet =xssfWorkbook.getSheetAt(numSheet);61if(xssfSheet ==null) {62continue;63}64//Read the Row65for(introwNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {66XSSFRow xssfRow =xssfSheet.getRow(rowNum);67if(xssfRow !=null) {68student =newStudent();69XSSFCell no = xssfRow.getCell(0);70XSSFCell name = xssfRow.getCell(1);71XSSFCell age = xssfRow.getCell(2);72XSSFCell score = xssfRow.getCell(3);73student.setNo(getValue(no));74student.setName(getValue(name));75student.setAge(getValue(age));76student.setScore(Float.valueOf(getValue(score)));77list.add(student);78}79}80}81returnlist;82}8384/**85* Read the Excel 2003-200786*@parampath the path of the Excel87*@return88*@throwsIOException89*/90@SuppressWarnings("resource")91publicList<Student> readXls(String path)throwsIOException {92System.out.println(Common.PROCESSING +path);93InputStream is =this.getClass().getResourceAsStream(path);94HSSFWorkbook hssfWorkbook =newHSSFWorkbook(is);95Student student =null;96List<Student> list =newArrayList<Student>();97//Read the Sheet98for(intnumSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {99HSSFSheet hssfSheet =hssfWorkbook.getSheetAt(numSheet);100if(hssfSheet ==null) {101continue;102}103//Read the Row104for(introwNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {105HSSFRow hssfRow =hssfSheet.getRow(rowNum);106if(hssfRow !=null) {107student =newStudent();108HSSFCell no = hssfRow.getCell(0);109HSSFCell name = hssfRow.getCell(1);110HSSFCell age = hssfRow.getCell(2);111HSSFCell score = hssfRow.getCell(3);112student.setNo(getValue(no));113student.setName(getValue(name));114student.setAge(getValue(age));115student.setScore(Float.valueOf(getValue(score)));116list.add(student);117}118}119}120returnlist;121}122123@SuppressWarnings("static-access")124privateString getValue(XSSFCell xssfRow) {125if(xssfRow.getCellType() ==xssfRow.CELL_TYPE_BOOLEAN) {126returnString.valueOf(xssfRow.getBooleanCellValue());127}elseif(xssfRow.getCellType() ==xssfRow.CELL_TYPE_NUMERIC) {128returnString.valueOf(xssfRow.getNumericCellValue());129}else{130returnString.valueOf(xssfRow.getStringCellValue());131}132}133134@SuppressWarnings("static-access")135privateString getValue(HSSFCell hssfCell) {136if(hssfCell.getCellType() ==hssfCell.CELL_TYPE_BOOLEAN) {137returnString.valueOf(hssfCell.getBooleanCellValue());138}elseif(hssfCell.getCellType() ==hssfCell.CELL_TYPE_NUMERIC) {139returnString.valueOf(hssfCell.getNumericCellValue());140}else{141returnString.valueOf(hssfCell.getStringCellValue());142}143}144}

/excel2html/src/main/java/com/b510/excel/util/Util.java

1packagecom.b510.excel.util;23importcom.b510.excel.common.Common;45publicclassUtil {67/**8* get postfix of the path9*@parampath10*@return11*/12publicstaticString getPostfix(String path) {13if(path ==null||Common.EMPTY.equals(path.trim())) {14returnCommon.EMPTY;15}16if(path.contains(Common.POINT)) {17returnpath.substring(path.lastIndexOf(Common.POINT) + 1, path.length());18}19returnCommon.EMPTY;20}21}

/excel2html/src/main/java/com/b510/excel/vm/student.vm

1<!DOCTYPE html>2<html>3<title>HTML Tutorial</title>4<style>5table{6width:100%;7}8table, th, td{9border:1px solid black;10border-collapse:collapse;11}12th, td{13padding:5px;14text-align:left;15}16table#t01 tr:nth-child(even){17background-color:#eee;18}19table#t01 tr:nth-child(odd){20background-color:#fff;21}22table#t01 th{23background-color:black;24color:white;25}26</style>27<body>2829<tableid="t01">30<tr>31<th>S/N</th>32<th>ID</th>33<th>Name</th>34<th>Age</th>35<th>Score</th>36</tr>37#set( $count = 1 )38#foreach( $student in $students)39<tr>40<td>$count</td>41<td>$student.no</td>42<td>$student.name</td>43<td>$student.age</td>44<td>$student.score</td>45</tr>46#set( $count = $count + 1 )47#end48</table>49</body>50</html>

/excel2html/src/main/java/com/b510/excel/vo/Student.java

1packagecom.b510.excel.vo;23publicclassStudent {45privateInteger id;6privateString no;7privateString name;8privateString age;9privatefloatscore;1011publicInteger getId() {12returnid;13}1415publicvoidsetId(Integer id) {16this.id =id;17}1819publicString getNo() {20returnno;21}2223publicvoidsetNo(String no) {24this.no =no;25}2627publicString getName() {28returnname;29}3031publicvoidsetName(String name) {32this.name =name;33}3435publicString getAge() {36returnage;37}3839publicvoidsetAge(String age) {40this.age =age;41}4243publicfloatgetScore() {44returnscore;45}4647publicvoidsetScore(floatscore) {48this.score =score;49}5051}

/excel2html/src/main/java/com/b510/excel/writer/WriteHtml.java

1packagecom.b510.excel.writer;23importjava.io.File;4importjava.io.FileWriter;5importjava.io.StringWriter;6importjava.util.List;78importorg.apache.velocity.Template;9importorg.apache.velocity.VelocityContext;10importorg.apache.velocity.app.VelocityEngine;1112importcom.b510.excel.common.Common;13importcom.b510.excel.vo.Student;1415publicclassWriteHtml {1617privatestaticString createCode(String fileVMPath, List<Student> students)throwsException {18VelocityEngine velocityEngine =newVelocityEngine();19velocityEngine.setProperty("input.encoding", "UTF-8");20velocityEngine.setProperty("output.encoding", "UTF-8");21velocityEngine.init();22Template template =velocityEngine.getTemplate(fileVMPath);23VelocityContext velocityContext =newVelocityContext();24velocityContext.put("students", students);25StringWriter stringWriter =newStringWriter();26template.merge(velocityContext, stringWriter);27returnstringWriter.toString();28}2930publicstaticvoidwrite(List<Student> students)throwsException {31System.out.println("write begin");32File file =newFile(Common.TEST_HTML_FILE);33FileWriter fw =newFileWriter(file);34fw.write(createCode("./src/main/java/com/b510/excel/vm/student.vm", students));35fw.flush();36fw.close();37System.out.println("write end. Refresh the project before seeing the excel2html/" +Common.HTML_FILE);38}39}

/excel2html/pom.xml

1<?xml version="1.0"?>2<project3xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"4xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">5<modelVersion>4.0.0</modelVersion>67<groupId>hongten.exl2html</groupId>8<artifactId>excel2html</artifactId>9<version>0.0.1</version>1011<name>excel2html</name>12<url>http://maven.apache.org</url>1314<properties>15<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>16</properties>1718<dependencies>19<dependency>20<groupId>junit</groupId>21<artifactId>junit</artifactId>22<version>3.8.1</version>23<scope>test</scope>24</dependency>2526<dependency>27<groupId>org.apache.poi</groupId>28<artifactId>poi</artifactId>29<version>3.12</version>30</dependency>3132<dependency>33<groupId>org.apache.poi</groupId>34<artifactId>poi-ooxml</artifactId>35<version>3.12</version>36</dependency>3738<dependency>39<groupId>org.apache.poi</groupId>40<artifactId>poi-ooxml-schemas</artifactId>41<version>3.12</version>42</dependency>4344<dependency>45<groupId>velocity</groupId>46<artifactId>velocity</artifactId>47<version>1.5</version>48</dependency>4950<dependency>51<groupId>dom4j</groupId>52<artifactId>dom4j</artifactId>53<version>1.5</version>54</dependency>5556<dependency>57<groupId>org.apache.xmlbeans</groupId>58<artifactId>xmlbeans</artifactId>59<version>2.6.0</version>60</dependency>6162</dependencies>63</project>

源码下载：http://files.cnblogs.com/files/hongten/excel2html.zip

========================================================