转载

Excel转Html

项目结构：

Excel转Html

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

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

Excel转Html

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

Excel转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>

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

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