serverあれこれ: H2Database

ラベル H2Database_ApachePOI の投稿を表示しています。すべての投稿を表示

2010年12月28日火曜日

H2 DatabaseとApache POIでExcelブックにシートを追加する関数を作成する

H2 DatabaseとApache POIでExcelブックにシートを追加する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_add_sheet as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_add_sheet(
  String inFile, String outFile, String sheetName)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( sheetName == null ){
    return -1;
  }

  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  for(int wi=0;wi<workbook.getNumberOfSheets();wi++){
    Sheet sheet = workbook.getSheetAt(wi);
    sheet.setSelected(false);
  }
  Sheet sheet = workbook.createSheet(sheetName);
  sheet.setSelected(true);
  workbook.setActiveSheet(workbook.getSheetIndex(sheet));
  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_add_sheet('c:\share\test1.xlsx', 
  'c:\share\test1h.xlsx', '追加');

入力Excelファイル(test1.xlsx)

出力Excelファイル(test1h.xlsx)

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

○動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月27日月曜日

H2 DatabaseとApache POIでExcelブックのシートにクエリー結果を保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのシートにクエリー結果を保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_queryresult as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_queryresult(java.sql.Connection conn,
  String inFile, String outFile, String sheetName,
  int oy, int ox, String query, Boolean header)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( query == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  int columns = 0;
  PreparedStatement stmt = conn.prepareStatement(query);
  ResultSet rset = null;
  Row rowobj = null;
  Cell cell = null;
  try
  {
    rset = stmt.executeQuery();
    ResultSetMetaData rsmd = rset.getMetaData();
    columns = rsmd.getColumnCount();
    if( header ){
      rowobj = sheet.getRow(oy);
      if( rowobj == null ){
        rowobj = sheet.createRow(oy);
      }
      for(int lx=0;lx<columns;lx++){
        cell = rowobj.getCell(ox+lx);
        if( cell == null ){
          cell = rowobj.createCell(ox+lx);
        }
        cell.setCellValue(rsmd.getColumnName(lx+1));
      }
      oy++;
    }
    while(rset.next()){
      rowobj = sheet.getRow(oy);
      if( rowobj == null ){
        rowobj = sheet.createRow(oy);
      }
      for(int lx=0;lx<columns;lx++){
        cell = rowobj.getCell(ox+lx);
        if( cell == null ){
          cell = rowobj.createCell(ox+lx);
        }
        if( rsmd.getColumnType(lx+1) == Types.DATE ){
          DataFormat df = workbook.getCreationHelper().createDataFormat();
          short dfn = df.getFormat("yyyy/mm/dd");
          CellStyle cs = workbook.createCellStyle();
          cs.setDataFormat(dfn);
          cell.setCellValue(new java.util.Date(
            rset.getDate(lx+1).getTime()));
          cell.setCellStyle(cs);
        } else {
          cell.setCellValue(rset.getString(lx+1));
        }
      }
      oy++;
    }
  }
  finally
  {
    if( rset != null ){ rset.close(); }
    stmt.close();
  }

  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_queryresult('c:\share\test1.xlsx', 
  'c:\share\test1g.xlsx', 'シート1', 3, 1, 
  'SELECT * FROM INFORMATION_SCHEMA.TABLES', TRUE);

入力Excel(test1.xlsx)

出力Excel(test1g.xlsx)

2010年12月26日日曜日

H2 DatabaseとApache POIでExcelブックのセルにハイパーリンクをセットして保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのセルにハイパーリンクをセットして保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_hyperlink as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_hyperlink(
  String inFile, String outFile, String sheetName,
  int row, int column, String label, String url)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( label == null ){
    return -1;
  }
  if( url == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    rowobj = sheet.createRow(row);
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    cell = rowobj.createCell(column);
  }
  // ハイパーリンクを作成
  Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(
    Hyperlink.LINK_URL);
  hyperlink.setLabel(label);
  hyperlink.setAddress(url);
  sheet.getRow(row).getCell(column).setCellValue(label);
  sheet.getRow(row).getCell(column).setHyperlink(hyperlink);

  // 一重下線と青色を設定
  Font font = workbook.createFont();
  font.setFontHeightInPoints((short)10);
  font.setFontName("ＭＳ ゴシック");
  font.setUnderline(Font.U_SINGLE);
  font.setColor(IndexedColors.BLUE.getIndex());
  CellStyle cs = workbook.createCellStyle();
  cs.setFont(font);
  sheet.getRow(row).getCell(column).setCellStyle(cs);

  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_hyperlink('c:\share\test1.xlsx', 
  'c:\share\test1f.xlsx', 'シート1', 2, 0, 
  'hyperlink', 'https://serverarekore.blogspot.com');

入力Excel(test1.xlsx)

出力Excel(test1f.xlsx)

2010年12月25日土曜日

H2 DatabaseとApache POIでExcelブックのセルにコメントをセットして保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのセルにコメントをセットして保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_comment as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_comment(
  String inFile, String outFile, String sheetName,
  int row, int column, String author, String comment)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( author == null ){
    author = "";
  }
  if( comment == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    rowobj = sheet.createRow(row);
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    cell = rowobj.createCell(column);
  }
  // コメントの作成
  CreationHelper ch = workbook.getCreationHelper();
  ClientAnchor anchor = ch.createClientAnchor();
  Drawing drawing = sheet.createDrawingPatriarch();
  Comment commentObj = drawing.createCellComment(anchor);
  commentObj.setString(ch.createRichTextString(comment));
  commentObj.setAuthor(author);
  cell.setCellComment(commentObj);

  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_comment('c:\share\test1.xlsx', 'c:\share\test1e.xlsx', 'シート1', 2, 0, 'test1', 'コメントです');

入力Excel(test1.xlsx)

出力Excel(test1e.xlsx)

2010年12月24日金曜日

H2 DatabaseとApache POIでExcelブックのセルに日付をセットして保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのセルに日付をセットして保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_date_value as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_date_value(
  String inFile, String outFile, String sheetName,
  int row, int column, java.sql.Date value)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( value == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    rowobj = sheet.createRow(row);
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    cell = rowobj.createCell(column);
  }

  DataFormat df = workbook.getCreationHelper().createDataFormat();
  short dfn = df.getFormat("yyyy/mm/dd");
  CellStyle cs = workbook.createCellStyle();
  cs.setDataFormat(dfn);
  cell.setCellValue(new java.util.Date(value.getTime()));
  cell.setCellStyle(cs);
  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_date_value('c:\share\test1.xlsx', 'c:\share\test1d.xlsx', 'シート1', 2, 0, current_date);

入力Excel(text1.xlsx)

出力Excel(text1d.xlsx)

2010年12月23日木曜日

H2 DatabaseとApache POIでExcelブックのセルにdouble型数値をセットして保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのセルにdouble型数値をセットして保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_double_value as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_double_value(
  String inFile, String outFile, String sheetName,
  int row, int column, Double value)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( value == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    rowobj = sheet.createRow(row);
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    cell = rowobj.createCell(column);
  }
  cell.setCellValue(value);
  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_double_value('c:\share\test1.xlsx', 'c:\share\test1c.xlsx', 'シート1', 2, 0, 1024);

入力Excelファイル(test1.xlsx)

出力Excelファイル(test1c.xlsx)

2010年12月22日水曜日

H2 DatabaseとApache POIでExcelブックのセルに文字列値をセットして保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのセルに文字列値をセットして保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_string_value as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_string_value(
  String inFile, String outFile, String sheetName,
  int row, int column, String value)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( value == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    rowobj = sheet.createRow(row);
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    cell = rowobj.createCell(column);
  }
  cell.setCellValue(value);
  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_string_value('c:\share\test1.xlsx', 'c:\share\test1b.xlsx', 'シート1', 2, 0, '更新');

入力Excelファイル(test1.xlsx)

出力Excelファイル(test1b.xlsx)

2010年12月21日火曜日

H2 DatabaseとApache POIでExcelブックのセル範囲の値を結果セットとして返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセル範囲の値を結果セットとして返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_data as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
SimpleResultSet poi_get_data(
  String file, String sheetName,
  int row, int column, int dy, int dx, 
  String dateFormat)
  throws Exception
{
  if( file == null ){
    return null;
  }
  if( dateFormat == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }

  SimpleResultSet rs = new SimpleResultSet();
  for(int lx=0;lx<dx;lx++){
    rs.addColumn("C" + lx, Types.VARCHAR, 2048, 0);
  }

  for(int ly=0;ly<dy;ly++){
    Object values[] = new Object[dx];
    for(int lx=0;lx<dx;lx++){
      Row rowobj = sheet.getRow(row+ly);
      if( rowobj == null ){
        return null;
      }
      Cell cell = rowobj.getCell(column+lx);
      if( cell == null ){
        return null;
      }
      String value = null;
      switch(cell.getCellType()){
      case Cell.CELL_TYPE_NUMERIC:
        CellStyle cs = cell.getCellStyle();
        if( cs != null ){
          String df = cs.getDataFormatString();
          if( df.indexOf('y') != -1 
            || df.indexOf('m') != -1
            || df.indexOf('d') != -1){
            java.util.Date dt = cell.getDateCellValue();
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
          value = sdf.format(dt);
          } else {
            value = "" + cell.getNumericCellValue();
          }
        }
        break;
      case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
      case Cell.CELL_TYPE_ERROR:
        value = "" + (int)cell.getErrorCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        value = "" + cell.getBooleanCellValue();
      }
      values[lx] = value;
    }
    rs.addRow(values);
  }
  return rs;
}
$$

実行例

select * from poi_get_data('c:\share\test2.xlsx', 'Sheet1', 3, 1, 3, 3, 'yyyy/mm/dd');

実行例結果画面

2010年12月20日月曜日

H2 DatabaseとApache POIでExcelブックのセルの値を文字列に変換して返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの値を文字列に変換して返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_cell_string as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_cell_string(
  String file, String sheetName,
  int row, int column, String dateFormat)
  throws Exception
{
  if( file == null ){
    return null;
  }
  if( dateFormat == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  switch(cell.getCellType()){
  case Cell.CELL_TYPE_NUMERIC:
    CellStyle cs = cell.getCellStyle();
    if( cs == null ){
      return null;
    }
    String df = cs.getDataFormatString();
    if( df.indexOf('y') != -1 
      || df.indexOf('m') != -1
      || df.indexOf('d') != -1){
      java.util.Date dt = cell.getDateCellValue();
      SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
      return sdf.format(dt);
    }
    return "" + cell.getNumericCellValue();
  case Cell.CELL_TYPE_STRING:
    return cell.getStringCellValue();
  case Cell.CELL_TYPE_ERROR:
    return "" + (int)cell.getErrorCellValue();
  case Cell.CELL_TYPE_FORMULA:
    return cell.getCellFormula();
  case Cell.CELL_TYPE_BOOLEAN:
    return "" + cell.getBooleanCellValue();
  }
  return null;
}
$$

実行例

select poi_get_cell_string('c:\share\test2.xlsx', 'Sheet1', 3, 1, 'yyyy/mm/dd');

2010年12月19日日曜日

H2 DatabaseとApache POIでExcelブックのセルの表示形式を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの表示形式を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_cellstyle_dataformat as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_cellstyle_dataformat(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  CellStyle cs = cell.getCellStyle();
  if( cs == null ){
    return null;
  }

  return cs.getDataFormatString();
}
$$

実行例

select poi_get_cellstyle_dataformat('c:\share\test2.xlsx', 'Sheet1', 9, 1);

2010年12月18日土曜日

H2 DatabaseとApache POIでExcelブックのリッチテキストセルの文字列を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのリッチテキストセルの文字列を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_richstring_cell_value as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_richstring_cell_value(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  if( cell.getCellType() != Cell.CELL_TYPE_STRING ){
    return null;
  }
  RichTextString rts = cell.getRichStringCellValue();
  if( rts == null ){
    return null;
  }

  return rts.getString();
}
$$

実行例

select poi_get_richstring_cell_value('c:\share\test2.xlsx', 'Sheet1', 9, 1);

2010年12月17日金曜日

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのアドレスを返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのアドレスを返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_hyperlink_address as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_hyperlink_address(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  Hyperlink hl = cell.getHyperlink();
  if( hl == null ){
    return null;
  }
  return hl.getAddress();
}
$$

実行例

select poi_get_hyperlink_address('c:\share\test2.xlsx', 'Sheet1', 16, 1);

2010年12月16日木曜日

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのラベルを返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのラベルを返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_hyperlink_label as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_hyperlink_label(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  Hyperlink hl = cell.getHyperlink();
  if( hl == null ){
    return null;
  }
  return hl.getLabel();
}
$$

実行例

select poi_get_hyperlink_label('c:\share\test2.xlsx', 'Sheet1', 16, 1);

2010年12月15日水曜日

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのタイプを返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルのハイパーリンクのタイプを返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_hyperlink_type as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_hyperlink_type(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  Hyperlink hl = cell.getHyperlink();
  if( hl == null ){
    return null;
  }
  String result = null;
  switch(hl.getType()){
  case Hyperlink.LINK_URL:
    result = "LINK_URL";
    break;
  case Hyperlink.LINK_DOCUMENT:
    result = "LINK_DOCUMENT";
    break;
  case Hyperlink.LINK_EMAIL:
    result = "LINK_EMAIL";
    break;
  case Hyperlink.LINK_FILE:
    result = "LINK_FILE";
    break;
  }
  return result;
}
$$

実行例

select poi_get_hyperlink_type('c:\share\test2.xlsx', 'Sheet1', 10, 1);

2010年12月14日火曜日

H2 DatabaseとApache POIでExcelブックのセルのコメントを返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルのコメントを返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_cell_comment as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_cell_comment(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  Comment comment = cell.getCellComment();
  if( comment == null ){
    return null;
  }
  RichTextString rtstr = comment.getString();
  if( rtstr == null ){
    return null;
  }
  return rtstr.getString();
}
$$

実行例

select poi_get_cell_comment('c:\share\test2.xlsx', 'Sheet1', 12, 1);

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月13日月曜日

H2 DatabaseとApache POIでExcelブックのセルの数式を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの数式を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_cell_formula as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_cell_formula(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  if( cell.getCellType() != Cell.CELL_TYPE_FORMULA ){
    return null;
  }
  return cell.getCellFormula();
}
$$

実行例

select poi_get_cell_formula('c:\share\test2.xlsx', 'Sheet1', 6, 3);

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月12日日曜日

H2 DatabaseとApache POIでExcelブックのセルの日付を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの日付を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_date_cell_value as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
Date poi_get_date_cell_value(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  if( cell.getCellType() != Cell.CELL_TYPE_NUMERIC ){
    return null;
  }
  java.util.Date dt = null;
  try
  {
    dt = cell.getDateCellValue();
  }
  catch(NumberFormatException nfex)
  {
    return null;
  }
  return new java.sql.Date(dt.getTime());
}
$$

実行例

select poi_get_date_cell_value('c:\share\test2.xlsx', 'Sheet1', 3, 1);

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月11日土曜日

H2 DatabaseとApache POIでExcelブックのセルの数値を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの数値を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_numeric_cell_value as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
Double poi_get_numeric_cell_value(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  if( cell.getCellType() != Cell.CELL_TYPE_NUMERIC ){
    return null;
  }
  return new Double(cell.getNumericCellValue());
}
$$

実行例

select poi_get_numeric_cell_value('c:\share\test1.xlsx', 'シート1', 1, 1);

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月10日金曜日

H2 DatabaseとApache POIでExcelブックのセルの文字列値を返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセルの文字列値を返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_string_cell_value as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
String poi_get_string_cell_value(
  String file, String sheetName,
  int row, int column)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }
  Row rowobj = sheet.getRow(row);
  if( rowobj == null ){
    return null;
  }
  Cell cell = rowobj.getCell(column);
  if( cell == null ){
    return null;
  }
  if( cell.getCellType() != Cell.CELL_TYPE_STRING ){
    return null;
  }
  return cell.getStringCellValue();
}
$$

実行例

select poi_get_string_cell_value('c:\share\test1.xlsx', 'シート1', 0, 1);

※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。

2010年12月9日木曜日

H2 DatabaseとApache POIでExcelブックのシートの名前を列挙する関数を作成する

H2 DatabaseとApache POIでExcelブックのシートの名前を列挙する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_list_sheets as $$ 
import java.io.*;
import java.sql.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
ResultSet poi_list_sheets(String file)
  throws Exception
{
  if( file == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));

  // ブックに含まれるシートの名前を列挙する
  SimpleResultSet rs = new SimpleResultSet();
  rs.addColumn("SHEET_INDEX", Types.INTEGER, 512, 0);
  rs.addColumn("NAME", Types.VARCHAR, 2048, 0);

  for(int wi=0;wi<workbook.getNumberOfSheets();wi++){
    rs.addRow(wi, workbook.getSheetName(wi));
  }
  return rs;
} 
$$

実行例

select * from poi_list_sheets('c:\share\test1.xlsx');

登録: 投稿 (Atom)

2010年12月28日火曜日

2010年12月27日月曜日

2010年12月26日日曜日

2010年12月25日土曜日

2010年12月24日金曜日

2010年12月23日木曜日

2010年12月22日水曜日

2010年12月21日火曜日

2010年12月20日月曜日

2010年12月19日日曜日

2010年12月18日土曜日

2010年12月17日金曜日

2010年12月16日木曜日

2010年12月15日水曜日

2010年12月14日火曜日

2010年12月13日月曜日

2010年12月12日日曜日

2010年12月11日土曜日

2010年12月10日金曜日

2010年12月9日木曜日

ラベル

ブログ アーカイブ

ブログアーカイブ