testoperationexcel.java

一个基本java下的操作excel的类

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

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;

public class TestOperationExcel {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// 创建对Excel工作簿文件的引用
		TestOperationExcel tt = new TestOperationExcel();
		//tt.readerExcel("d:\\aa.xls");//测试读
		//tt.writeExcel("d:\\bb.xls");//测试写
	}

	public void readerExcel(String filepath) {
		System.err.println("进入excel文件！！！！！");
		String fileToBeRead = filepath;
		HSSFWorkbook workbook;
		try {
			workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));

			// 创建对工作表的引用。
			// 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"） 我用的是下一种方法
			HSSFSheet sheet = workbook.getSheetAt(0);
			// 也可用getSheetAt(int index)按索引引用，
			// 在Excel文档中，第一张工作表的缺省索引是0，
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			// int x取到的是当前工作簿的记录条数减一
			// String re = "";
			int x = sheet.getLastRowNum();
			// long y是每天的毫秒数，后面取特殊的日期型的数据用
			// long y = [phone];

			// 下面是设置现在要取得是第一行
			HSSFRow row;
			// 目前的 排列位置是 班级名称 学号 姓名
			// 下面是设置现在要取得是这一行的第一个位置
			for (int i = 1; i <= x; i++) {
				System.err.println(i+"--------------------------------------");
				row = sheet.getRow(i);
				String[] fields = new String[row.getLastCellNum()];
				for(int j = 0 ; j < row.getLastCellNum(); j++){
					String str = getCellValue(row.getCell((short)j));
					System.err.print("','"+str+"','");
				}
				System.err.println("");
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private String getCellValue(HSSFCell cell) {
		String ret = "";
		if (cell != null) {
			if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
				ret = "" + cell.getNumericCellValue(); // 1
			} else {
				ret = cell.getStringCellValue().trim(); // 1
			}
		}

		return ret;
	}

	public void writeExcel(String filepath) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		try {
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
			titleStyle.setBorderLeft((short) 1);
			titleStyle.setBorderRight((short) 1);
			// titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
			// titleStyle.setFillForegroundColor(org.apache.poi.hssf.util.HSSFColor.LIGHT_ORANGE.index);
			// titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFSheet sheet = workbook.createSheet();
			HSSFRow row = sheet.createRow((short) 0);
			// 写首行(标题);
			String[] fileds = new String[] { "a", "b", "c", "d" };//标题数据
			HSSFCell cell = row.createCell((short) 0);
			for (int f = 0; f < fileds.length; f++) {
				cell = row.createCell((short) f);
				cell.setCellStyle(titleStyle);
				cell.setCellType(HSSFCell.ENCODING_UTF_16);
				cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
				cell.setCellValue(fileds[f]);
			}
			//这里写要写入到Excel的数据。
			Collection col_sum = new ArrayList();
			for (int ii = 0; ii < 10; ii++) {
				fileds = new String[] { ii + "a", ii + "b", ii + "c", ii + "d", };
				col_sum.add(fileds);
			}
			// 写数据行到Excel
			int i = 0;
			for (Iterator iter_sum = col_sum.iterator(); iter_sum.hasNext(); i++) {
				String[] temp = (String[]) iter_sum.next();
				row = sheet.createRow((short) i + 1);
				for (int f = 0; f < temp.length; f++) {
					cell = row.createCell((short) f);
					// cell.setCellStyle(titleStyle);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
					cell.setCellValue(temp[f]);
				}
			}

			FileOutputStream fOut = new FileOutputStream(filepath);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
			System.err.println("报表已生成!!!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}