Excel转Html

Hongten

发布于 2022-05-06 10:57:23

2.6K0

发布于 2022-05-06 10:57:23

文章被收录于专栏：HongtenHongten

项目结构：

这是一个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

Excel转Html

Excel转Html

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐