工作笔记：maven项目-Excel to HTML

package com.chinadaas.riskbell;

import java.io.*;

/**
 * Created by pc on 2017/3/13.
 */
public class ToHtml {

    public ToHtml(){

    }
    public static void main(String[] args){
        //输入类型判断
        BufferedReader bf=new BufferedReader(new InputStreamReader(System.in));
        System.out.println("请输入要进行的操作编号（1、2、3）：\n1、单个文件转换\n2、文件夹转换\n3、结束");
        String type="";
        while(true){
            try{
                type=bf.readLine();
                if(type.equals("1")==true){
                    System.out.println("你输入的是单个文件转换："+type);
                    //单个文件转换函数
                    System.out.print("请输入文件目录地址：");
                    BufferedReader b=new BufferedReader(new InputStreamReader(System.in));
                    String dirPath =b.readLine();
                    File file = new File(dirPath);
                    Els_x_toHtml els_x_toHtml=new Els_x_toHtml(dirPath,"F:\\"+file.getName()+".html");
                    System.out.println("转化后的输出路径为："+"F:\\"+file.getName()+".html");
                }
                else if(type.equals("2")==true){
                    System.out.println("你输入的是目录转换："+type);
                    //多文件转换接口
                    ToHtml toHtml=new ToHtml();

                    //循环调用单个文件转换
                    System.out.print("请输入文件目录地址：");
                    BufferedReader bf2=new BufferedReader(new InputStreamReader(System.in));
                    String dirPath2 =bf2.readLine();
                    System.out.print("请输入文件输出目录地址：");
                    BufferedReader bf3=new BufferedReader(new InputStreamReader(System.in));
                    String dirPath3 =bf3.readLine();
                    Dir_todir dir_todir = new Dir_todir(dirPath2,dirPath3);

                }
                else if(type.equals("3")==true){
                    System.out.println("结束"+type);
                    break;
                }
                else{
                    System.out.println("你输入的不是有效内容");
                    System.out.println("请输入要进行的操作编号（1、2、3）：\n1、单个文件转换\n2、文件夹转换\n3、结束");
                }
            } catch (IOException e) {   e.printStackTrace();}
        }
        System.out.print("done");
    }

}



package com.chinadaas.riskbell;

import java.io.File;
import java.io.IOException;

/**
 * Created by pc on 2017/3/13.
 */
public class Dir_todir {

    private  String indirPath=null;
    private  String outdirPath=null;
    private File outPath;
    private File inPath;

    public Dir_todir (String indirPath,String outdirPath){
        this.indirPath=indirPath;
        this.outdirPath=outdirPath;
        this.inPath=new File(indirPath);
        this.outPath= new File(outdirPath);
        if (inPath.exists()) {
                         if (inPath.isDirectory()) {
                                 System.out.println("输入路径存在");
                             try {
                                 createdir();
                                 getFileList(indirPath,indirPath,outdirPath);
                             } catch (Exception e) {
                                 e.printStackTrace();
                             }
                             } else {
                                 System.out.println("输入路径不是文件夹");
                             }
                     } else {
                         System.out.println("输入路径不存在");
                     }
                     System.out.print("请按任意键继续");
    }

    /**创建路径*/
    public void createdir() throws IOException{
        String lastName =inPath.getName();
        outPath = new File(outdirPath);
        String outPutPathName =outPath.getPath()+"\\"+lastName;  //输出的路径名
        outPath=new File(outPutPathName);
        outPath.mkdirs();
    }
    /**
     * 为文件生成多级目录
     *@param strPath,源目录文件根地址
     *@param ipath 源文件地址（用于剪切字符）
     *@param opath 目标文件根目录
     */
    public void getFileList(String strPath,String ipath,String opath)throws IOException {
        File dir = new File(strPath);
        File dirin = new File(ipath);
        File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
        if (files != null) {
            for (int i = 0; i < files.length; i++) {
                String fileName = files[i].getName();
                if (files[i].isDirectory()) { // 判断是文件还是文件夹,如果是文件夹获取在子目录中的文件夹名称
                    String filename = files[i].getAbsolutePath().replace(dirin.getAbsolutePath(),"") ;
                    File outp = new File(opath+"\\"+dirin.getName()+filename);
                    outp.mkdirs();
                    getFileList(files[i].getAbsolutePath(),ipath,opath); // 迭代
                } else if (fileName.endsWith("xlsx")) { // 判断文件名是否以.xlsx结尾
                    String strFileName = files[i].getName();
                    String strFiledir = files[i].getAbsolutePath();
                    //加工文件名称  1、去除输入路径的文件名：F:\AAAA\AAASS\A.XLDX----->F:\AAAA\AAASS\
                    String str1=strFiledir.replace(strFileName,"");
                    //加工文件名称  2、去除输入路径的根目录：F:\AAAA\AAASS\------>\AAASS\
                    String str2=str1.replace(ipath,"");
                    //加工文件名称  3、增加输出目录的根目录：\AAASS\------>F:\asd\AAASS\
                    String str3=opath+"\\"+dirin.getName()+str2+strFileName.substring(0,strFileName.indexOf(".xlsx")) + ".html";
                    //输出文件  Els_x_toHtml（输入路径，输出路径）
                    File filetest = new File(str3);
                    filetest.mkdirs();
                    Els_x_toHtml els_x_toHtml_xlsx = new Els_x_toHtml(strFiledir,str3+"\\"+strFileName+".html");

                }else if (fileName.endsWith("xls")) { // 判断文件名是否以.xls结尾
                    String strFileName = files[i].getName();
                    String strFiledir = files[i].getAbsolutePath();
                    //加工文件名称  1、去除输入路径的文件名：F:\AAAA\AAASS\A.XLDX----->F:\AAAA\AAASS\
                    String str1=strFiledir.replace(strFileName,"");
                    //加工文件名称  2、去除输入路径的根目录：F:\AAAA\AAASS\------>\AAASS\
                    String str2=str1.replace(ipath,"");
                    //加工文件名称  3、增加输出目录的根目录：\AAASS\------>F:\asd\AAASS\
                    String str3=opath+"\\"+dirin.getName()+str2+strFileName.substring(0,strFileName.indexOf(".xls")) + ".html";
                    //输出文件  Els_x_toHtml（输入路径，输出路径）
                    File filetest = new File(str3);
                    filetest.mkdirs();
                    Els_x_toHtml els_x_toHtml_xls = new Els_x_toHtml(strFiledir,str3+"\\"+strFileName+".html");
                } else {
                    continue;
                }
            }
        }
    }
}




package com.chinadaas.riskbell;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Formatter;

/**
 * Created by pc on 2017/3/13.
 * @para 输入路径（文件名），输出路径（文件夹名）
 * 完成文件的对应输出
 *
 */
public class Els_x_toHtml {

    private final Workbook wb;
    private HSSFWorkbook hwb;
    private XSSFWorkbook xwb;
    private Appendable output;
    private Formatter out;
    private int sheetNo;
    private String sheetNames[];

    private String outputPath;
    private String inputPath;

    public Els_x_toHtml(String inputPath,String outputPath) throws IOException{
       this.outputPath=outputPath;
       this.inputPath=inputPath;
            try {
                wb = WorkbookFactory.create(new FileInputStream(inputPath));
                output= new PrintWriter(new FileWriter(outputPath));
                if (wb == null)
                    throw new NullPointerException("wb");
                if (output == null)
                    throw new NullPointerException("output");
                sheetNo = wb.getNumberOfSheets();
                getNames(sheetNo);
                printPage();
            } catch (Exception e){
                throw new IllegalArgumentException("Cannot create workbook from stream", e);
            }
        }
    private void constructTabHeader()
    {
        out.format("<div id=\"main\">");
        out.format("<div class=\"ui-widget-header ui-corner-top\" >%n");
        out.format("<ul>%n");
        for(int i=0;i<sheetNo;i++)
        {
            out.format("<li>%n");
            out.format("<a href =\"#tabs-"+(i+1)+"\">"+sheetNames[i]+"</a>%n");
            out.format("</li>%n");
        }
        out.format("</div>");
    }

    private void getNames(int num)
    {
        sheetNames = new String[num];
        for(int i=0;i<num;i++)
        {
            String name = wb.getSheetName(i);
            sheetNames[i]=name;
        }
    }

    //判断Excel版本
    private void printsheets() throws IOException{
        if (wb instanceof HSSFWorkbook)
        {
            hwb=new HSSFWorkbook(new FileInputStream(inputPath));
            int sheetnum=hwb.getNumberOfSheets();
            for(int nums=0;nums<sheetnum;nums++){
                HSSFSheet sheet = hwb.getSheetAt(nums);
                String name = sheet.getSheetName();
                int numr =sheet.getPhysicalNumberOfRows();
                int numc =sheet.getLeftCol();
                out.format("<p>工作页码"+nums+"</p>\n" +
                        "<p>.工作空间名称:"+name+"</p>\n"+
                        "<p>行列数："+ numr+" ,"+numc+"<p>");
                for(int i=0;i<numr;i++){
                    //System.out.print("i:"+i);
                    out.format("<table ><tr>\n");
                    HSSFRow hssfRow = sheet.getRow(i);
                    numc=hssfRow.getPhysicalNumberOfCells();
                    for(int j=0;j<numc;j++){
                        //System.out.print("j:"+j);
                        HSSFCell cell =hssfRow.getCell(j);
                        String str =getCellValue(cell);
                        out.format("<td>"+str+"</td>\n" );
                    }
                    out.format("</tr>\n</table>");
                }
            }
        }
        else if (wb instanceof XSSFWorkbook)
        {
            xwb=new XSSFWorkbook(new FileInputStream(inputPath));
            int sheetnum=xwb.getNumberOfSheets();
            for(int nums=0;nums<sheetnum;nums++){
                XSSFSheet sheet = xwb.getSheetAt(nums);
                String name = sheet.getSheetName();
                int numr =sheet.getPhysicalNumberOfRows();
                int numc =sheet.getLeftCol();
                out.format("<p>工作页码"+nums+"</p>\n" +
                        "<p>.工作空间名称:"+name+"</p>\n"+
                        "<p>行列数："+ numr+" ,"+numc+"<p>");
                for(int i=0;i<numr;i++){
                    XSSFRow xssfRow = sheet.getRow(i);
                    numc=xssfRow.getPhysicalNumberOfCells();
                    out.format("<table ><tr>\n");
                    for(int j=0;j<numc;j++){
                        XSSFCell cell =xssfRow.getCell(j);
                        String str =getCellValue(cell);
                        out.format("<td>"+str+"</td>\n" );
                    }
                    out.format("</tr>\n</table>");
                }
            }
        }
        else
            throw new IllegalArgumentException(
                    "unknown workbook type: " + wb.getClass().getSimpleName());
    }

    public void printPage() throws IOException {
        try {
            ensureOut();
            int sheetnum = wb.getNumberOfSheets();
            out.format("<!DOCTYPE html>%n");
            out.format("<html>%n");
            out.format("<head>%n");
            out.format("<meta charset=\"utf-8\">%n");
            out.format("<title>Excel转换HTML测试version1</title>%n");
            out.format("</head>%n");
            out.format("<body>%n");
            out.format("<style type=\"text/css\">\n" +
                    "td\n" +
                    "  {\n" +
                    "  height:18px;\n" +
                    "  width:130px;\n" +
                    "  vertical-align:middle;\n" +
                    "  }table,th\n" +
                    "  {\n" +
                    "  border: 1px solid black;\n" +
                    "  font-size:10px;\n" +
                    "  }table\n" +
                    "  {\n" +
                    "  border-collapse:collapse;\n" +
                    "  width:100%%;\n" +
                    "  }\n" +
                    "  </style>\n" +
                    "  </body>");

            printsheets();
            if (true) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
        } finally {
            if (out != null)
                out.close();
            if (output instanceof Closeable) {
                Closeable closeable = (Closeable) output;
                closeable.close();
                wb.close();
            }
        }
    }

    private void ensureOut() {
        if (out == null)
            out = new Formatter(output);
    }

    private String getCellValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    short format = cell.getCellStyle().getDataFormat();
                    if(format == 14 || format == 31 || format == 57 || format == 58){   //excel中的时间格式
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellvalue = sdf.format(date);
                    }
                    // 判断当前的cell是否为Date
                    else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。
                        // 如果是Date类型则，取得该Cell的Date值           // 对2014-02-02格式识别不出是日期格式
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue= formater.format(date);
                    } else { // 如果是纯数字
                        // 取得当前Cell的数值
                        cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                    break;
                case  HSSFCell.CELL_TYPE_BLANK:
                    cellvalue = "";
                    break;
                // 默认的Cell值
                default:{
                    cellvalue = "";
                }
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
}