你的位置:首页 > Java教程

[Java教程]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

 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 excel15     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 Row104       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 path10    * @return11   */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 #end48 </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.

 1 <??> 2 <project 3   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" 4   ="http://maven.apache.org/POM/4.0.0" ="http://www.w3.org/2001/> 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-oo</artifactId>35       <version>3.12</version>36     </dependency>37 38     <dependency>39       <groupId>org.apache.poi</groupId>40       <artifactId>poi-oo</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.</groupId>58       <artifactId></artifactId>59       <version>2.6.0</version>60     </dependency>61 62   </dependencies>63 </project>

 

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

 

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

More reading,and english is important.

I'm Hongten

hongten

 

E | hongtenzone@foxmail.com  B | http://www.cnblogs.com/hongten

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