处理大型Excel文件的Java类库: X4J Analytic

jopen 11年前

x4j-analytic是一个用于Java编程语言的开源XLSX格式模板引擎API。X4J可嵌入在Java应用程序库,作为实现完全成熟的报表解决方案。X4J拥有很高的性能,能够使用恒定内存在数秒内产生一百万行的Excel报表。

X4J主输入/输出格式是XLSX,Excel是作为设计编辑模板。也可以导出其他格式的报表:PDF,HTML,XML,CSV。

package x4j.samples;  import static com.exigeninsurance.x4j.analytic.util.MockResultSet.cols;  import static com.exigeninsurance.x4j.analytic.util.MockResultSet.data;  import static com.exigeninsurance.x4j.analytic.util.MockResultSet.row;    import java.io.File;  import java.sql.Connection;  import java.sql.PreparedStatement;  import java.sql.ResultSet;  import java.sql.SQLException;  import java.sql.Statement;  import java.text.NumberFormat;    import org.h2.jdbcx.JdbcDataSource;  import org.junit.Test;    import com.exigeninsurance.x4j.analytic.api.DefaultReportDataProvider;  import com.exigeninsurance.x4j.analytic.api.ReportContext;  import com.exigeninsurance.x4j.analytic.api.ReportDataProvider;  import com.exigeninsurance.x4j.analytic.api.X4JEngine;  import com.exigeninsurance.x4j.analytic.util.MockReportDataProvider;  import com.exigeninsurance.x4j.analytic.util.MockResultSet;    /**  * It is X4JEngine sample implemented as JUnit test.  * Code demonstrates typical X4J Analytic usage to implement light weight reporting solution.  *  * Samples save output to samples/target directory  *  * @author jbaliuka  *  */    public class X4JEngineTest {                /**           * HelloWorld.xlsx template contains ${message} expression,           * it should evaluate to <i>Hello World !</i> in output file           *           */          @Test          public void helloWorld(){                    X4JEngine engine = new X4JEngine();                            ReportContext context = engine.createContext("samples/HelloWorld.xml");                  File saveTo = new File("target/HelloWorld.xlsx");                                      context.getParameters().put("message", "Hello World !");                  engine.transaform(context,saveTo);          }            /**           * MockData sample demonstrates mock data source for unit testing,           * MockData.xlsx file contains Excel table (table name is Table1).           * Table should be populated from query element with the same name.           * Normally query string contains SQL but it might be any script or URL to call WebService,           *           *           * This sample produces report in XLSX and PDF formats           */          @Test          public void mockData() {                    mockData("pdf");                          mockData("xlsx");            }            public void mockData(String format) {                    X4JEngine engine = new X4JEngine();                  setupMockDataSource(engine);                    ReportContext context = engine.createContext("samples/MockData.xml");                  context.setOutputFormat(format);                    File saveTo = new File("target/MockData." + format);                                    engine.transaform(context,saveTo);            }              /**           * PivotReport sample demonstrates pivot report,           * it uses same mock data but one of sheets contains pivot.           * Pivot should refresh data from Excel table           */          @Test          public void pivotReport() {                      X4JEngine engine = new X4JEngine();                  setupMockDataSource(engine);                    ReportContext context = engine.createContext("samples/PivotReport.xml");                  File saveTo = new File("target/PivotReport.xlsx");                                    engine.transaform(context,saveTo);            }                              /**           * Demonstrates style override without changes in template           *           */          @Test          public void overrideStyles() {                      X4JEngine engine = new X4JEngine();                  engine.getStyles().add("samples/customStyles.xlsx");                                    setupMockDataSource(engine);                    ReportContext context = engine.createContext("samples/PivotReport.xml");                                    context.setTableStyleName("customTableStyle");                  context.setPivotStyleName("customPivotStyle");                                    File saveTo = new File("target/OverrideStyles.xlsx");                                    engine.transaform(context,saveTo);            }            /**           * Scripting.xlsx template contains ${reportMetadata.name} expression,           * it should evaluate to report name defined Scripting.xml file           *           */          @Test          public void scripting(){                    X4JEngine engine = new X4JEngine();                            ReportContext context = engine.createContext("samples/Scripting.xml");                  File saveTo = new File("target/Scripting.xlsx");                                    engine.transaform(context,saveTo);          }            /**           * h2DataSource example use h2 DB connection for data access           *           */          @Test          public void h2DataSource() throws Exception{                    X4JEngine engine = new X4JEngine();                  Connection connection = getConnection();                  try{                          puplateDB(connection);                          engine.setDataProvider( new DefaultReportDataProvider(connection) );                            ReportContext context = engine.createContext("samples/h2Datasource.xml");                          context.getParameters().put("top_premium", 0);                            File saveTo = new File("target/h2Datasource.xlsx");                                          engine.transaform(context,saveTo);                            drop(connection);                  }finally{                          connection.close();                  }            }            /**           * RollupReport example demonstrates advanced #for loop and currency formatting           *           */          @Test          public void rollupReport() throws Exception{                    X4JEngine engine = new X4JEngine();                  Connection connection = getConnection();                  try{                          puplateDB(connection);                          engine.setDataProvider( new DefaultReportDataProvider(connection) );                            ReportContext context = engine.createContext("samples/RollupReport.xml");                          context.setOutputFormat("pdf");                          context.getParameters().put("formatter", NumberFormat.getCurrencyInstance());                            File saveTo = new File("target/RollupReport.pdf");                                          engine.transaform(context,saveTo);                            drop(connection);                  }finally{                          connection.close();                  }            }                private void drop(Connection connection) throws SQLException {                  Statement statement = connection.createStatement();                  try {                          statement.execute(" DROP TABLE POLICY_SUMMARY");                          }finally{                          statement.close();                  }              }            private void puplateDB(Connection connection) throws SQLException {                    Statement statement = connection.createStatement();                  try {                          statement.execute(                                          " CREATE TABLE POLICY_SUMMARY( " +                                                          " PRODUCT VARCHAR(255), " +                                                          " POLICY CHAR(7) , " +                                                          " STATE CHAR(2) , " +                                                          " PREMIUM DECIMAL(9,2) " +                                                          " ) "                                          );                          }finally{                          statement.close();                  }                    PreparedStatement pstatement =         connection.prepareStatement(                                  "INSERT INTO POLICY_SUMMARY VALUES (?,?,?,?)"                                  );                  try {                          for(Object[] nextRow : data ){                                  int i = 0;                                  for(Object next : nextRow ){                                          pstatement.setObject(++i, next);                                  }                                  pstatement.execute();                          }                          connection.commit();                  }finally{                          pstatement.close();                  }              }            private void setupMockDataSource(X4JEngine engine){                                    ResultSet rs = MockResultSet.create(cols,data);                  ReportDataProvider dataProvider = new MockReportDataProvider(rs);                  engine.setDataProvider(dataProvider);            }              private Connection getConnection() throws Exception{                    return ds.getConnection();            }            private JdbcDataSource ds = new JdbcDataSource();          {                  ds.setURL("jdbc:h2:mem:db");                  ds.setUser("sa");                  ds.setPassword("sa");          }            private String[] cols =                          cols("PRODUCT","POLICY", "STATE","PREMIUM");          private Object[][] data = data(                          row("Auto", "AU25636","CA",200),                          row("Home", "HO25636","CA",200),                          row("Auto", "AU12345","NY",195),                          row("Home", "HO23145","NY",186),                          row("Auto", "AU74125","CA",193),                          row("Auto", "AU74135","NM",198),                          row("Auto", "AU72135","NC",198),                          row("Auto", "AU72135","NC",198),                                                  row("Auto", "AU25636","CA",200),                          row("Home", "HO29636","CA",200),                          row("Auto", "AU12745","NY",195),                          row("Home", "HO03145","NY",186),                          row("Auto", "AU70125","CA",193),                          row("Auto", "AU70135","NM",198),                          row("Auto", "AU70135","NC",198),                          row("Auto", "AU70135","NC",198),                                                  row("Auto", "AU25630","CA",200),                          row("Home", "HO25630","CA",200),                          row("Auto", "AU12340","NY",195),                          row("Home", "HO23140","NY",186),                          row("Auto", "AU74120","CA",193),                          row("Auto", "AU74350","NM",198),                          row("Auto", "AU72350","NC",198),                          row("Auto", "AU72350","NC",198),                                                  row("Auto", "AU25360","CA",200),                          row("Home", "HO29360","CA",200),                          row("Auto", "AU12450","NY",195),                          row("Home", "HO03450","NY",186),                          row("Auto", "AU70250","CA",193),                          row("Auto", "AU70350","NM",198),                          row("Auto", "AU70350","NC",198),                          row("Auto", "AU70350","NC",198)                          );    }



项目主页:http://www.open-open.com/lib/view/home/1382787816562