java - 如何将CellStyle对象存储到数据库中？

Question

我正在读取 Excel 文件并存储一些属性，如单元格样式、列宽、行和列索引并存储在 map 中，如下所示:

package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Skeleton {

    public Map<Integer, List<List<Object>>> readSkeleton(File input){

        Map<Integer, List<List<Object>>> skeletondata = new TreeMap<Integer, List<List<Object>>>();
        try {
            FileInputStream in = new FileInputStream(input);
            XSSFWorkbook wb = new XSSFWorkbook(in);

            int sheetIx = 5;   //remove if using above for loop
                XSSFSheet st = wb.getSheetAt(sheetIx); 
                int rowcount = 0;
                for (Row row:st){

                    List<List<Object>> skeletonrow = new ArrayList<List<Object>>();

                    int cellcount = 0;
                    for (Cell cell:row){

                        List<Object> skeletoncell = new ArrayList<Object>();

                        skeletoncell.add(sheetIx);   //for sheet Ix
                        skeletoncell.add(cell.getRowIndex());  //for rowIx
                        skeletoncell.add(cell.getColumnIndex());  //for columnIx

                        CellStyle cs = cell.getCellStyle();
                        int columnwidth = st.getColumnWidth(cellcount);
                        skeletoncell.add(cs);  // for cell style

                        skeletoncell.add(columnwidth);  //for column width

                        switch (cell.getCellType()) {

                        /*case Cell.CELL_TYPE_BLANK:
                            skeletoncell.add(null);
                            skeletonrow.add(skeletoncell);
                            break; 
                        case Cell.CELL_TYPE_BOOLEAN:                        
                            break;
                        case Cell.CELL_TYPE_ERROR:                                          
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            break;         */
                        case Cell.CELL_TYPE_NUMERIC:
                            skeletoncell.add(cell.toString());
                            skeletonrow.add(skeletoncell);
                            break;
                        case Cell.CELL_TYPE_STRING:
                            skeletoncell.add(cell.getStringCellValue());
                            //skeletoncell.add("Abrakadabra");
                            skeletonrow.add(skeletoncell);
                            break;
                        default:
                            skeletoncell.add(null);
                            skeletonrow.add(skeletoncell);
                            break;

                        }
                        System.out.println("skeleton cell size: "+skeletoncell.size());
                        cellcount++;
                    }
                    skeletondata.put(rowcount, skeletonrow);
                    rowcount++;
                }
                System.out.println("skeleton data :"+skeletondata);




        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return skeletondata;



    }
}

这将返回一个 map 元素，其中包含行号作为键，每个单元格及其属性作为值。我试图将这些数据存储到数据库(postgres)中，如下所示:

package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Skeleton {
    public void skeletonDataToDatabase(File input){

        DAOClass dao = new DAOClass();
        Connection con = null;
        PreparedStatement pst = null;

        con = dao.getConnection();

        try{
            Skeleton skeleton = new Skeleton();
            Map<Integer, List<List<Object>>> skeletondata = new TreeMap<Integer, List<List<Object>>>();
            skeletondata = skeleton.readSkeleton(input);
            Set<Integer> keys = skeletondata.keySet();
            for (Integer key : keys){

                List<List<Object>> skeletonrow = new ArrayList<List<Object>>();
                skeletonrow = skeletondata.get(key);

                for (int r=0;r<skeletonrow.size();r++){ 

                    List<Object> skeletoncell = new ArrayList<Object>();
                    skeletoncell = skeletonrow.get(r);

                    XSSFWorkbook wb = new XSSFWorkbook();
                    CellStyle cs1 = (CellStyle) skeletoncell.get(3);

                    //cs1.cloneStyleFrom((CellStyle) skeletoncell.get(3));  // cell style value
                    System.out.println("cwll style: "+cs1);
                       /*Schd_Id integer, 
                       SubSchd_Id integer, 
                       RowIx integer, 
                       ColIx integer, 
                       CellStyle_Value character varying(100), 
                       ColumnWidth integer,
                       Cell_Value character varying(100)*/
                    //System.out.println("fifth value: "+skeletoncell.get(5));
                    if(skeletoncell.get(5)==null){    //check for null cell value (blank)
                        //System.out.println("after if loop true ");
                        String query = "insert into Template_Skeleton(Schd_Id,SubSchd_Id,RowIx,ColIx,CellStyle_Value,ColumnWidth) " +
                                "values(?,?,?,?,?,?);";
                        pst = con.prepareStatement(query);
                        pst.setInt(1, 1);                       //Schd id
                        pst.setInt(2, (int) skeletoncell.get(0));                       //Subschd id
                        pst.setInt(3, (int) skeletoncell.get(1));  //row Ix
                        pst.setInt(4, (int) skeletoncell.get(2));  //col ix
                        pst.setObject(5, cs1);                //cellstyle value
                        pst.setInt(6, (int) skeletoncell.get(4));  //column width

                    }else{
                        System.out.println("inside else loop false");
                        String query = "insert into Template_Skeleton(Schd_Id,SubSchd_Id,RowIx,ColIx,CellStyle_Value,ColumnWidth,Cell_Value) " +
                                "values(?,?,?,?,?,?,?);";
                        //System.out.println("after query");
                        pst = con.prepareStatement(query);
                        pst.setInt(1, 1);                       //Schd id
                        pst.setInt(2, (int) skeletoncell.get(0));                       //Subschd id
                        pst.setInt(3, (int) skeletoncell.get(1));  //row Ix
                        pst.setInt(4, (int) skeletoncell.get(2));  //col ix
                        pst.setObject(5, cs1);  //cellstyle value
                        pst.setInt(6, (int) skeletoncell.get(4));  //column width
                        pst.setString(7, (String) skeletoncell.get(5));  //cell calue
                        //System.out.println("after 7th value");

                    }   
                    //System.out.println("before execute");
                    pst.executeUpdate();
                    //System.out.println("after execute");

                }
                System.out.println("inserted row :"+key);

            }

        }catch (SQLException e){
            e.printStackTrace();

        }

    }
}

执行时显示以下错误:

org.postgresql.util.PSQLException:无法推断用于 org.apache.poi.xssf.usermodel.XSSFCellStyle 实例的 SQL 类型。使用带有显式 Types 值的 setObject() 来指定要使用的类型。在 org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1917)在 org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)在 com.tcs.Skeleton.sculptureDataToDatabase(Skeleton.java:157)在 com.tcs.Test.main(Test.java:121)

注意:main方法在测试类中，从DAOclass连接。我尝试将 cellstyle 对象添加为字符串，但我想将其存储为字符串，因为表单数据库我必须渲染样式以创建遵循存储样式的新工作表。提前致谢。

Answer 1

我建议序列化单元格样式对象并存储序列化值。我通常使用 Jackson用于序列化/反序列化。单元格数据不应该很大，因此序列化为字符串应该可以。您可以使用大型 varchar 列或 CLOB 列。