java读写Excel util

 

package util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/*
 * 　1、创建字体，设置其为红色、粗体：
 hssffont font = workbook.createfont();
 font.setcolor(hssffont.color_red);
 font.setboldweight(hssffont.boldweight_bold);
 2、创建格式
 hssfcellstyle cellstyle= workbook.createcellstyle();
 cellstyle.setfont(font);
 3、应用格式
 hssfcell cell = row.createcell((short) 0);
 cell.setcellstyle(cellstyle);
 cell.setcelltype(hssfcell.cell_type_string);
 cell.setcellvalue("标题 ");
 */

public class ExcelHelp {
 private HSSFWorkbook fWorkbook = null;

 private HSSFSheet sheet = null;

 private FileInputStream fis = null;

 Map maxColLenthMap = new HashMap();

 // 
 // public crateStyle(){
 // HSSFFont font = fWorkbook.createFont();
 // font.setColor((HSSFFont.COLOR_RED);
 // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 //  
 // HSSFCellStyle cellStyle= Workbook.createcellstyle();
 // cellStyle.setFont(font);
 //  
 // }

 public ExcelHelp(String filePath) {
  try {
   fis = new FileInputStream(filePath);
   fWorkbook = new HSSFWorkbook(fis);
   sheet = fWorkbook.getSheetAt(0);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 public ExcelHelp(InputStream is) {
  try {
   fWorkbook = new HSSFWorkbook(is);
   sheet = fWorkbook.getSheetAt(0);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 

 private HSSFCell getCell(short r, short c) {
  HSSFRow row = sheet.getRow(r);
  HSSFCell cell = row.getCell(c);
  return cell;
 }

 private String readStr(HSSFCell cell) {
  if (cell == null)
   return null;
  if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
   return "";
  } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
   double d = cell.getNumericCellValue();
   DecimalFormat df = new DecimalFormat("#");
   return df.format(d);
  }
  String content = cell.getStringCellValue();
  return content==null?"":content.trim();
 }

 private double readDouble(HSSFCell cell) {
  if (cell == null)
   return -1;
  return cell.getNumericCellValue();
 }

 private Date readDate(HSSFCell cell) {
  if (cell == null)
   return null;
  return cell.getDateCellValue();
 }

 private HSSFCellStyle readCellStyle(HSSFCell cell) {
  if (cell == null)
   return null;
  return cell.getCellStyle();
 }

 public String readStr(short r, short c) throws IOException {
  return readStr(getCell(r, c));
 }

 public double readDouble(short r, short c) throws IOException {
  return readDouble(getCell(r, c));
 }

 public Date readDate(short r, short c) throws IOException {
  return readDate(getCell(r, c));
 }

 public HSSFCellStyle readCellStyle(short r, short c) throws IOException {
  return readCellStyle(getCell(r, c));
 }

 private HSSFCell getCellNoEmpty(short r, short c) {
  HSSFRow row = sheet.getRow(r);
  if (row == null) {
   row = sheet.createRow(r);

  }
  HSSFCell cell = row.getCell(c);
  if (cell == null) {
   cell = row.createCell(c);
  }
  cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  return cell;
 }

 public void writeStr(short r, short c, String value) {
  HSSFCell cell = getCellNoEmpty(r, c);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  cell.setCellValue(value);

 }

 public void writeStr1(short r, short c, String value) {
  writeStr(r, c, value);
  String _maxLen = (String) maxColLenthMap.get("" + c);
  if (_maxLen != null) {
   int _l = Integer.parseInt(_maxLen);
   if (value.getBytes().length * 256 > _l) {
    _maxLen = "" + value.getBytes().length * 256;
    maxColLenthMap.put("" + c, _maxLen);
   }
  } else {
   try {
    _maxLen = "" + value.getBytes("GBK").length * 256;
    maxColLenthMap.put("" + c, _maxLen);
   } catch (UnsupportedEncodingException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }

 public void writeDouble(short r, short c, double value) {
  HSSFCell cell = getCellNoEmpty(r, c);
  cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
  cell.setCellValue(value);
 }

 public void setCellStyle(short r, short c, HSSFCellStyle style) {
  HSSFCell cell = getCellNoEmpty(r, c);
  cell.setCellStyle(style);
 }

 public void appStyle(short r1, short c1, short r, short c) {
  HSSFCellStyle cellStyle = readCellStyle(getCell(r, c));
  setCellStyle(r1, c1, cellStyle);
 }

 public void saveExcel(OutputStream os) throws IOException {

  fWorkbook.write(os);
 }

 public void close() throws IOException {
  fis.close();
 }

 public void setColumnWidth(short col, short len) {

  sheet.setColumnWidth(col, len);
 }

 public void appColWidth() {
  for (Iterator iter = maxColLenthMap.keySet().iterator(); iter.hasNext();) {
   String key = (String) iter.next();
   int col = Integer.parseInt(key);
   int colLength = Integer.parseInt((String) maxColLenthMap.get(key));
   setColumnWidth((short) col, (short) colLength);
  }
 }

 public static void main(String[] args) throws IOException {
  short row = 3;
  short col = 3;
  // System.out.println(help.readExcel(0, row, col));
  ExcelHelp help = new ExcelHelp("c:\\123.xls");
  HSSFCellStyle style = help.readCellStyle((short) 0, (short) 0);

//  help.writeStr(row, col,
//    "中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文");
  help.writeStr((short)0, (short)0, ""+10000000);
  if (style instanceof java.io.Serializable) {
   System.out.println("111111111111111111111111111");   
  }
//  help.setCellStyle(row, col, style);
//  help.writeStr((short) (row + 1), (short) (col + 1), "中1234");
//  help
//    .setColumnWidth(
//      (short) (row),
//      (short) ("中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文中1111文"
//        .getBytes().length * 256));
  help.saveExcel(new FileOutputStream("c:\\行政.xls"));
 }

}