| コンポーネント POIFS(POI Filesystem) 共通API HSSF(Horrible Spreadsheet Format) Excel用 HDF(Horrible Document Format) Word用 HPSF(Horrible Property Set Format) 文書プロパティ(題名、最終更新日など)用 |
| 実行環境 |
| OS:Microsoft Windows Me / Microsoft Excel 2000 |
| 明日のための其の壱 Excelの読み書き |
| C:\javaに下記ファイル作成しJavaクラスの実行 |
| ソースファイル：C:\java\poitest.java |
| import java.io.*; import org.apache.poi.hssf.usermodel.*; public class poitest { public static void main(String[] args) throws Exception { HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream("読込.xls")); System.out.println("Sheet" + workBook.getNumberOfSheets()); // ワークシートのループ for(int i=0; i<workBook.getNumberOfSheets(); i++) { HSSFSheet workSheet = workBook.getSheetAt(i); System.out.println("-- " + workBook.getSheetName(i) + " --"); // 行のループ for(int j=0; j<=workSheet.getLastRowNum(); j++) { HSSFRow row = workSheet.getRow(j); // 列のループ for(int k=0; k<row.getLastCellNum(); k++) { HSSFCell cell = row.getCell((short)k); // セルがNULLの場合(何も値が入っていない場合)は、表示処理を飛ばす if(cell == null) continue; switch(cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: // セルの値が文字型の場合 System.out.println(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: // セルの値が数値型の場合 System.out.println(cell.getNumericCellValue()); break; default: System.out.println("..."); } } } } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow((short)2); // Create a cell and put a value in it. HSSFCell cell = row.createCell((short)0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("漢字①～"); // Or do it on one line. row.createCell((short)1).setCellValue(1.2); row.createCell((short)2).setCellValue("This is a string"); row.createCell((short)3).setCellValue(true); cell = row.createCell((short)4); //cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("漢字②－"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("書込.xls"); wb.write(fileOut); fileOut.close(); } } |
| コンパイルファイル：C:\java\cpl.bat |
| set classpath=.;C:\jakarta-poi-1.5.1-final-bin\build\jakarta-poi-1.5.1-final-20020615.jar C:\j2sdk1.4.0_01\bin\javac -deprecation poitest.java pause |
| 実行ファイル：C:\java\run.bat |
| set classpath=.;C:\jakarta-poi-1.5.1-final-bin\build\jakarta-poi-1.5.1-final-20020615.jar C:\j2sdk1.4.0_01\bin\java poitest pause |
| 実行結果：DOSプロンプト |
| C:\java>set classpath=.;C:\jakarta-poi-1.5.1-final-bin\build\jakarta-poi-1.5.1-f inal-20020615.jar C:\java>C:\j2sdk1.4.0_01\bin\java poitest Sheet1 -- testpoi -- 0.1 123.0 character ① 全角 C:\java>pause 続けるにはどれかキーを押してください . . . |
| Excel |
 |
| 明日のための其の弐 Excelのオブジェクトコピー |
| C:\javaに下記ファイル作成しJavaクラスの実行 |
| ソースファイル：C:\java\poitest.java |
| import java.io.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.poifs.filesystem.*; class poitest { public static void main(String[] args) throws Exception { HSSFWorkbook wb = null; HSSFCell cell = null; HSSFRow row = null; try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("c:\\java\\in.xls")); wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); System.out.println("Sheet Name " + wb.getSheetName(0)); row = sheet.createRow((short)0); //行 cell = row.createCell((short)0); //列 cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("漢字①～"); FileOutputStream fileOut = new FileOutputStream("c:\\java\\out.xls"); wb.write(fileOut); fileOut.close(); System.out.println("End"); } catch (Exception e) { System.out.println("Exception"); } } } |
| サンプル１ |
| public static void main(String[] args) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); HSSFCellStyle style = wb.createCellStyle(); for (int i = 0 ; i < 1000 ; i++) { for (int j = 0 ; j < 15 ; j++) {{llStyle();;Exception HSSFRow row = sheet.createRow((short)i); HSSFCell cell = row.createCell((short)j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(""); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(style); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); cell.setCellStyle(style); } } FileOutputStream fileOut = new FileOutputStream( "d:\\down\\workbook.xls" ); wb.write( fileOut ); fileOut.close(); System.out.println("end"); } |
| サンプル２ |
| public static void main(String[] args) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle cellStyle = null; row = sheet.createRow((short)0); cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getFormat("0.00")); cell = row.createCell((short)0); cell.setCellValue(6.9); cell.setCellStyle(cellStyle); cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm")); cell = row.createCell((short)1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); row.createCell((short) 2).setCellValue("a string"); row.createCell((short) 3).setCellValue(true); cell = row.createCell((short)4); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue("漢字①～"); FileOutputStream fileOut = new FileOutputStream( "d:\\down\\workbook.xls" ); wb.write( fileOut ); fileOut.close(); System.out.println("end"); } |