quickexcel.java

excel打印模板,支持单表,主从关系表格,利用xml格式的office文档特点

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company:倚天软件 </p>
 *
 * @author 丁锋
 * @version 1.0
 */


import java.sql.*;
import java.util.*;
import java.io.*;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;

/**
 *
 * @author Administrator
 */
public class QuickExcel {

    /** Creates a new instance of QuickExcel */
    private QuickExcel(String file) {
        _file = file;
    }

    private void open() throws IOException {
        InputStream stream = null;
        Record[] records = null;
        POIFSFileSystem fs =
                new POIFSFileSystem(new FileInputStream(_file));
        _wb = new HSSFWorkbook(fs);
    }

    private void create() {
        _wb = new HSSFWorkbook();
    }

    public static QuickExcel newInstance(String file) {
        QuickExcel qe = new QuickExcel(file);
        qe.create();
        return qe;
    }

    public static QuickExcel openInstance(String file) throws IOException {
        QuickExcel qe = new QuickExcel(file);
        qe.open();
        return qe;
    }

    public void close() {
        try {
            FileOutputStream fileOut = new FileOutputStream(_file);
            _wb.write(fileOut); //把Workbook对象输出到文件workbook.xls中
            fileOut.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
    }

    private void removeSheet(String sheetName) {
        int i = _wb.getSheetIndex(sheetName);
        if (i >= 0) _wb.removeSheetAt(i);
    }

    public int fillSheet(ResultSet rs, String sheetName, int left, int top) throws
            SQLException {
        HSSFSheet st = _wb.getSheet(sheetName);
        ResultSetMetaData rsmd = rs.getMetaData();
        int index = top;
        int result = 0;
        int rowNum = st.getLastRowNum();

        row = st.getRow(index);
        int markRowNum = row.getLastCellNum();
        String temp = "";
        markVc = new Vector();
        sysVc = new Vector();
        boolean isSysVc = false;
        for (int i = 0; i < markRowNum; i++) {
            cell = row.getCell((short) i);
            temp = cell.getStringCellValue();
            System.out.println("temp before substring= " + temp + " i= " + i);
            //if (temp.indexOf("#") != -1) {
            //temp = temp.substring(1, temp.length());
            //System.out.println("temp after substring= " + temp);
            if (temp.length() != 0) {
                markVc.add(temp);
            }

            //   }
        }

        while (rs.next()) {
            result++;
            // int rowNum = st.getLastRowNum();
            System.out.println("last row number: " + rowNum);
            if (rowNum > index) {
                row = st.getRow(index++);
                int cellNum = row.getLastCellNum();
                System.out.println("markVc 长度: " + markVc.size());

                for (int i = 0; i < markVc.size(); ++i) {
                    if (cellNum > i + left) {
                        String temp1 = this.markVc.elementAt(i).toString();
                        System.out.println("markVc toString = " + temp1);
                        cell = row.getCell((short) (i + left));
                        cell.setEncoding(cell.ENCODING_UTF_16);
                        cell.setCellValue(rs.getString(temp1));
                    } else {
                        System.out.println("操作过程中,第" + result +
                                           "次插入操作,从被操作的单元格开始,从左到右计算,第" + i +
                                           "个单元格未初始化，请检查模板。");

                    }
                    //cell.setCellStyle(cellStyle);
                }
            } else {
                System.out.println("Excel模板中有未初始化的单元格行数不够，请检查模板。");
            }
        }
        return result;
    }


    public int fillSheet(Hashtable ht, String sheetName) throws
            SQLException {
        HSSFSheet st = _wb.getSheet(sheetName);
        //  ResultSetMetaData rsmd = rs.getMetaData();
        int index = 0;
        int result = 0;
        int rowNum = st.getLastRowNum();
        for (int i = 0; i < rowNum; i++) {
            row = st.getRow(i);
            int cellNum = row.getLastCellNum();
            for (int m = 0; m < cellNum; m++) {
                cell = row.getCell((short) m);
                String cellValue = cell.getStringCellValue();
                if (cellValue.indexOf("&") != -1) {
                    cellValue = cellValue.substring(1, cellValue.length());
                    int h = ht.size();
                    String sysPara = ht.get(cellValue).toString();
                    // System.out.println("sysPara= " + sysPara);
                    cell.setEncoding(cell.ENCODING_UTF_16);
                    cell.setCellValue(sysPara);

                }
                System.out.println("cellValue= " + cellValue);

            }

        }

        return 1;
    }

    public int fillSheet(ResultSet rs, String sheetName) throws
            SQLException {

        HSSFSheet st = _wb.getSheet(sheetName);
        //  ResultSetMetaData rsmd = rs.getMetaData();
        int index = 0;
        int result = 0;
        int rowNum = st.getLastRowNum();
        for (int i = 0; i < rowNum; i++) {
            row = st.getRow(i);
            int cellNum = row.getLastCellNum();
            for (int m = 0; m < cellNum; m++) {
                cell = row.getCell((short) m);
                String cellValue = cell.getStringCellValue();
                if (cellValue.indexOf("#") != -1) {
                    System.out.println("cellValue= " + cellValue);

                    cellValue = cellValue.substring(1, cellValue.length());
                    System.out.println("cellValue= " + cellValue);
                    while (rs.next()) {
                        String sysPara = rs.getString(cellValue);
                        cell.setEncoding(cell.ENCODING_UTF_16);
                        cell.setCellValue(sysPara);
                    }

                }
                System.out.println("cellValue= " + cellValue);

            }

        }

        return 1;
    }

    public static void main(String[] args) {

    }

    HSSFWorkbook _wb;
    HSSFRow row;
    HSSFCell cell;
    String _file = "";
    Vector markVc;
    Vector sysVc;
}