Excel转Html

 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 }

 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 }

  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 }

 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 }

 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>

 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 }

 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 }

 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>