项目结构:
这是一个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
1 package com.b510.excel.client;
2
3 import java.util.List;
4
5 import com.b510.excel.common.Common;
6 import com.b510.excel.reader.ReadExcel;
7 import com.b510.excel.vo.Student;
8 import com.b510.excel.writer.WriteHtml;
9
10 public class Client {
11
12 public static void main(String[] args) throws Exception {
13 String excel2010 = Common.STUDENT_INFO_XLSX_PATH;
14 // read the 2010 excel
15 List<Student> list1 = new ReadExcel().readExcel(excel2010);
16 if (list1 != null && list1.size() > 0) {
17 for (Student student : list1) {
18 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore());
19 }
20 System.out.println("begin to write into html file");
21 WriteHtml.write(list1);
22 }
23
24 }
25 }
/excel2html/src/main/java/com/b510/excel/common/Common.java
1 package com.b510.excel.common;
2
3 public class Common {
4
5 public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
6
7 public static final String EMPTY = "";
8 public static final String POINT = ".";
9 public static final String STUDENT_INFO_XLSX_PATH = "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
10 public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
11 public static final String PROCESSING = "Processing...";
12
13 public static final String HTML_FILE = "test.html";
14 public static final String TEST_HTML_FILE = "./test.html";
15
16 }
/excel2html/src/main/java/com/b510/excel/reader/ReadExcel.java
1 package com.b510.excel.reader;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.util.ArrayList;
6 import java.util.List;
7
8 import org.apache.poi.hssf.usermodel.HSSFCell;
9 import org.apache.poi.hssf.usermodel.HSSFRow;
10 import org.apache.poi.hssf.usermodel.HSSFSheet;
11 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
12 import org.apache.poi.xssf.usermodel.XSSFCell;
13 import org.apache.poi.xssf.usermodel.XSSFRow;
14 import org.apache.poi.xssf.usermodel.XSSFSheet;
15 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
16
17 import com.b510.excel.common.Common;
18 import com.b510.excel.util.Util;
19 import com.b510.excel.vo.Student;
20
21 public class ReadExcel {
22
23 /**
24 * read the Excel file
25 * @param path the path of the Excel file
26 * @return
27 * @throws IOException
28 */
29 public List<Student> readExcel(String path) throws IOException {
30 if (path == null || Common.EMPTY.equals(path)) {
31 return null;
32 } else {
33 String postfix = Util.getPostfix(path);
34 if (!Common.EMPTY.equals(postfix)) {
35 if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
36 return readXlsx(path);
37 }
38 } else {
39 System.out.println(path + Common.NOT_EXCEL_FILE);
40 }
41 }
42 return null;
43 }
44
45 /**
46 * Read the Excel 2010
47 * @param path the path of the excel file
48 * @return
49 * @throws IOException
50 */
51 @SuppressWarnings("resource")
52 public List<Student> readXlsx(String path) throws IOException {
53 System.out.println(Common.PROCESSING + path);
54 InputStream is = this.getClass().getResourceAsStream(path);
55 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
56 Student student = null;
57 List<Student> list = new ArrayList<Student>();
58 // Read the Sheet
59 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
60 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
61 if (xssfSheet == null) {
62 continue;
63 }
64 // Read the Row
65 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
66 XSSFRow xssfRow = xssfSheet.getRow(rowNum);
67 if (xssfRow != null) {
68 student = new Student();
69 XSSFCell no = xssfRow.getCell(0);
70 XSSFCell name = xssfRow.getCell(1);
71 XSSFCell age = xssfRow.getCell(2);
72 XSSFCell score = xssfRow.getCell(3);
73 student.setNo(getValue(no));
74 student.setName(getValue(name));
75 student.setAge(getValue(age));
76 student.setScore(Float.valueOf(getValue(score)));
77 list.add(student);
78 }
79 }
80 }
81 return list;
82 }
83
84 /**
85 * Read the Excel 2003-2007
86 * @param path the path of the Excel
87 * @return
88 * @throws IOException
89 */
90 @SuppressWarnings("resource")
91 public List<Student> readXls(String path) throws IOException {
92 System.out.println(Common.PROCESSING + path);
93 InputStream is = this.getClass().getResourceAsStream(path);
94 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
95 Student student = null;
96 List<Student> list = new ArrayList<Student>();
97 // Read the Sheet
98 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
99 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
100 if (hssfSheet == null) {
101 continue;
102 }
103 // Read the Row
104 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
105 HSSFRow hssfRow = hssfSheet.getRow(rowNum);
106 if (hssfRow != null) {
107 student = new Student();
108 HSSFCell no = hssfRow.getCell(0);
109 HSSFCell name = hssfRow.getCell(1);
110 HSSFCell age = hssfRow.getCell(2);
111 HSSFCell score = hssfRow.getCell(3);
112 student.setNo(getValue(no));
113 student.setName(getValue(name));
114 student.setAge(getValue(age));
115 student.setScore(Float.valueOf(getValue(score)));
116 list.add(student);
117 }
118 }
119 }
120 return list;
121 }
122
123 @SuppressWarnings("static-access")
124 private String getValue(XSSFCell xssfRow) {
125 if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
126 return String.valueOf(xssfRow.getBooleanCellValue());
127 } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
128 return String.valueOf(xssfRow.getNumericCellValue());
129 } else {
130 return String.valueOf(xssfRow.getStringCellValue());
131 }
132 }
133
134 @SuppressWarnings("static-access")
135 private String getValue(HSSFCell hssfCell) {
136 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
137 return String.valueOf(hssfCell.getBooleanCellValue());
138 } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
139 return String.valueOf(hssfCell.getNumericCellValue());
140 } else {
141 return String.valueOf(hssfCell.getStringCellValue());
142 }
143 }
144 }
/excel2html/src/main/java/com/b510/excel/util/Util.java
1 package com.b510.excel.util;
2
3 import com.b510.excel.common.Common;
4
5 public class Util {
6
7 /**
8 * get postfix of the path
9 * @param path
10 * @return
11 */
12 public static String getPostfix(String path) {
13 if (path == null || Common.EMPTY.equals(path.trim())) {
14 return Common.EMPTY;
15 }
16 if (path.contains(Common.POINT)) {
17 return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
18 }
19 return Common.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>
5 table {
6 width:100%;
7 }
8 table, th, td {
9 border: 1px solid black;
10 border-collapse: collapse;
11 }
12 th, td {
13 padding: 5px;
14 text-align: left;
15 }
16 table#t01 tr:nth-child(even) {
17 background-color: #eee;
18 }
19 table#t01 tr:nth-child(odd) {
20 background-color:#fff;
21 }
22 table#t01 th {
23 background-color: black;
24 color: white;
25 }
26 </style>
27 <body>
28
29 <table id="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 #end
48 </table>
49 </body>
50 </html>
/excel2html/src/main/java/com/b510/excel/vo/Student.java
1 package com.b510.excel.vo;
2
3 public class Student {
4
5 private Integer id;
6 private String no;
7 private String name;
8 private String age;
9 private float score;
10
11 public Integer getId() {
12 return id;
13 }
14
15 public void setId(Integer id) {
16 this.id = id;
17 }
18
19 public String getNo() {
20 return no;
21 }
22
23 public void setNo(String no) {
24 this.no = no;
25 }
26
27 public String getName() {
28 return name;
29 }
30
31 public void setName(String name) {
32 this.name = name;
33 }
34
35 public String getAge() {
36 return age;
37 }
38
39 public void setAge(String age) {
40 this.age = age;
41 }
42
43 public float getScore() {
44 return score;
45 }
46
47 public void setScore(float score) {
48 this.score = score;
49 }
50
51 }
/excel2html/src/main/java/com/b510/excel/writer/WriteHtml.java
1 package com.b510.excel.writer;
2
3 import java.io.File;
4 import java.io.FileWriter;
5 import java.io.StringWriter;
6 import java.util.List;
7
8 import org.apache.velocity.Template;
9 import org.apache.velocity.VelocityContext;
10 import org.apache.velocity.app.VelocityEngine;
11
12 import com.b510.excel.common.Common;
13 import com.b510.excel.vo.Student;
14
15 public class WriteHtml {
16
17 private static String createCode(String fileVMPath, List<Student> students) throws Exception {
18 VelocityEngine velocityEngine = new VelocityEngine();
19 velocityEngine.setProperty("input.encoding", "UTF-8");
20 velocityEngine.setProperty("output.encoding", "UTF-8");
21 velocityEngine.init();
22 Template template = velocityEngine.getTemplate(fileVMPath);
23 VelocityContext velocityContext = new VelocityContext();
24 velocityContext.put("students", students);
25 StringWriter stringWriter = new StringWriter();
26 template.merge(velocityContext, stringWriter);
27 return stringWriter.toString();
28 }
29
30 public static void write(List<Student> students) throws Exception {
31 System.out.println("write begin");
32 File file = new File(Common.TEST_HTML_FILE);
33 FileWriter fw = new FileWriter(file);
34 fw.write(createCode("./src/main/java/com/b510/excel/vm/student.vm", students));
35 fw.flush();
36 fw.close();
37 System.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 <project
3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"
4 xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
5 <modelVersion>4.0.0</modelVersion>
6
7 <groupId>hongten.exl2html</groupId>
8 <artifactId>excel2html</artifactId>
9 <version>0.0.1</version>
10
11 <name>excel2html</name>
12 <url>http://maven.apache.org</url>
13
14 <properties>
15 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
16 </properties>
17
18 <dependencies>
19 <dependency>
20 <groupId>junit</groupId>
21 <artifactId>junit</artifactId>
22 <version>3.8.1</version>
23 <scope>test</scope>
24 </dependency>
25
26 <dependency>
27 <groupId>org.apache.poi</groupId>
28 <artifactId>poi</artifactId>
29 <version>3.12</version>
30 </dependency>
31
32 <dependency>
33 <groupId>org.apache.poi</groupId>
34 <artifactId>poi-ooxml</artifactId>
35 <version>3.12</version>
36 </dependency>
37
38 <dependency>
39 <groupId>org.apache.poi</groupId>
40 <artifactId>poi-ooxml-schemas</artifactId>
41 <version>3.12</version>
42 </dependency>
43
44 <dependency>
45 <groupId>velocity</groupId>
46 <artifactId>velocity</artifactId>
47 <version>1.5</version>
48 </dependency>
49
50 <dependency>
51 <groupId>dom4j</groupId>
52 <artifactId>dom4j</artifactId>
53 <version>1.5</version>
54 </dependency>
55
56 <dependency>
57 <groupId>org.apache.xmlbeans</groupId>
58 <artifactId>xmlbeans</artifactId>
59 <version>2.6.0</version>
60 </dependency>
61
62 </dependencies>
63 </project>
源码下载:https://files.cnblogs.com/files/hongten/excel2html.zip
More reading,and english is important.
I'm Hongten
大哥哥大姐姐,觉得有用打赏点哦!多多少少没关系,一分也是对我的支持和鼓励。谢谢。
Hongten博客排名在100名以内。粉丝过千。
Hongten出品,必是精品。
E | hongtenzone@foxmail.com B | http://www.cnblogs.com/hongten