jxl 엑셀 수정 예제

package ulca.common.util;

import java.io.File;
import java.util.Date;
import jxl.*;
import jxl.write.*;

public class ExcelModify {
 public static boolean excelModify ( int cols, int rows, jxl.write.WritableWorkbook copy, int sheetnum, String labelstr ) throws Exception
 {
  jxl.write.WritableSheet sheet = copy.getSheet ( sheetnum );
  jxl.write.WritableCell cell = sheet.getWritableCell ( cols, rows );
  if ( cell.getType () == jxl.CellType.LABEL ) {
   // 방식 1
   // jxl.write.Label label = new jxl.write.Label(cols, rows,labelstr);
   // sheet.addCell(label);
   // 방식 2
   jxl.write.Label label = ( jxl.write.Label ) cell;
   label.setString ( labelstr );
  }
  else if ( cell.getType () == jxl.CellType.EMPTY ) {
   jxl.write.Label label = new jxl.write.Label ( cols, rows, labelstr );
   sheet.addCell ( label );
  }
  else {
   throw new Exception ( "not correct String format type" );
  }
  return true;
 }

 public static boolean excelModify ( int cols, int rows, jxl.write.WritableWorkbook copy, int sheetnum, double numd ) throws Exception
 {
  jxl.write.WritableSheet sheet = copy.getSheet ( sheetnum );
  jxl.write.WritableCell cell = sheet.getWritableCell ( cols, rows );
  if ( cell.getType () == jxl.CellType.NUMBER ) {
   jxl.write.Number n = ( jxl.write.Number ) cell;
   n.setValue ( numd );
  }
  else if ( cell.getType () == jxl.CellType.EMPTY ) {
   jxl.write.Number n = new jxl.write.Number ( cols, rows, numd );
   sheet.addCell ( n );
  }
  else {// System.out.println(cell);
   throw new Exception ( "not correct Number format type" );
  }
  return true;
 }

 public static boolean excelModify ( int cols, int rows, jxl.write.WritableWorkbook copy, int sheetnum, double numd, String numberformat ) throws Exception
 {
  jxl.write.WritableSheet sheet = copy.getSheet ( sheetnum );
  jxl.write.WritableCell cell = sheet.getWritableCell ( cols, rows );
  jxl.write.NumberFormat fivedps = new jxl.write.NumberFormat ( numberformat );
  jxl.write.WritableCellFormat cellFormat = new jxl.write.WritableCellFormat ( fivedps );
  if ( cell.getType () == jxl.CellType.NUMBER ) {
   cell.setCellFormat ( cellFormat );
   jxl.write.Number n = ( jxl.write.Number ) cell;
   n.setValue ( numd );
  }
  else if ( cell.getType () == jxl.CellType.EMPTY ) {
   jxl.write.Number n = new jxl.write.Number ( cols, rows, numd, cellFormat );
   sheet.addCell ( n );
  }
  else {
   throw new Exception ( "not correct Number format type" );
  }
  return true;
 }

 public static jxl.write.WritableWorkbook getCopy ( java.io.File infile, java.io.File outfile ) throws Exception
 {
  jxl.Workbook workbook = jxl.Workbook.getWorkbook ( infile );
  jxl.write.WritableWorkbook copy = jxl.Workbook.createWorkbook ( outfile, workbook );
  return copy;
 }

 public static void close ( jxl.write.WritableWorkbook copy ) throws Exception
 {
  copy.write ();
  copy.close ();
 }

 // 원본엑셀을 수정하는 예제
 public static void main ( String[] args ) throws Exception
 {
  ExcelModify rm = new ExcelModify ();
  java.io.File infile = new java.io.File ( "원본엑셀.xls" );
  java.io.File outfile = new java.io.File ( "output.xls" );
  jxl.write.WritableWorkbook copy = rm.getCopy ( infile, outfile );
  rm.excelModify ( 3, 4, copy, 0, "안녕" );
  rm.excelModify ( 3, 5, copy, 0, 250.23 );
  rm.excelModify ( 3, 50, copy, 0, 45464640, "#,###" );
  rm.close ( copy );
  System.out.println ( outfile.length () );
 }

 public static String excelRead ( int cols, int rows, jxl.Workbook workbook, int sheetnum ) throws Exception
 {
  jxl.Sheet sheet = workbook.getSheet ( sheetnum );
  jxl.Cell a1 = sheet.getCell ( cols, rows );
  String temp_str = a1.getContents ();
  if ( temp_str == null ) temp_str = "";
  return temp_str.trim ();
 }

 public static void excelWrite ( int cols, int rows, jxl.write.WritableWorkbook workbook, jxl.write.WritableSheet sheet, String labelstr ) throws Exception
 {
  jxl.write.Label label = new jxl.write.Label ( cols, rows, labelstr );
  sheet.addCell ( label );
 }

 public static jxl.Workbook getWorkbook ( java.io.File file ) throws Exception
 {
  jxl.Workbook workbook = jxl.Workbook.getWorkbook ( file );
  return workbook;
 }

 public static jxl.write.WritableWorkbook getWritableWorkbook ( java.io.File file ) throws Exception
 {
  jxl.write.WritableWorkbook workbook = jxl.Workbook.createWorkbook ( file );
  return workbook;
 }

 public static jxl.write.WritableSheet getSheet ( String sheetname, int sheetnum, jxl.write.WritableWorkbook workbook ) throws Exception
 {
  return workbook.createSheet ( sheetname, sheetnum );
 }

 public static void create ( jxl.write.WritableWorkbook workbook ) throws Exception
 {
  workbook.write ();
  workbook.close ();
 }
};