java 使用工具读取Excel文件

java 操作Excel 文件主要有3个工具

jxl，jcom和poi，三个工具的介绍自己到网上搜一下有很多，不在累述

 

jxl：

package com.junl.scott;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;

public class ExcelImportJxl
{
	static String sourceFile = "D:/功能说明文档.xls"; // 源文件

	public static void main(String[] args)
	{
		try
		{
			Workbook book = Workbook.getWorkbook(new File(sourceFile));

			// 0代表第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			String colname1 = sheet.getCell(0, 0).getContents().trim();
			String colname2 = sheet.getCell(1, 0).getContents().trim();
			String colname3 = sheet.getCell(2, 0).getContents().trim();
			System.out.println(colname1 + "," + colname2 + "," + colname3);
			for (int z = 1; z < rows; z++)
			{
				// 0代表列数，z代表行数
				for (int j = 0; j < cols; j++)
				{
					System.out.print(sheet.getCell(j, z).getContents() + "   ");
				}
				System.out.println();
				System.out.println("***************************************");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
}

jcom：现在还有一个问题没有解决，就是不能获取具体文件的行数和列数，所以读取的时候还不能动态读取，只能手动设置值

package com.junl.scott;

import java.io.File;
import java.util.Date;

import jp.ne.so_net.ga2.no_ji.jcom.excel8.*;
import jp.ne.so_net.ga2.no_ji.jcom.*;

public class ExcelImportJcom
{
	public static void main(String[] args) throws Exception
	{
		JCOMReadExcel();
		// JCOMCreateExcel();
	}

	static void JCOMReadExcel()
	{
		ReleaseManager rm = new ReleaseManager();

		try
		{
			System.out.println(System.getProperty("java.library.path"));
			System.out.println("EXCEL startup...");
			// if already started, open new window
			ExcelApplication excel = new ExcelApplication(rm);
			excel.Visible(false);
			String Filename = "D:/物资经销分中心补库申请人、审核人、审批人编号、名字、权限.xls";
			ExcelWorkbooks xlBooks = excel.Workbooks();
			ExcelWorkbook xlBook = xlBooks.Open(Filename);

			ExcelWorksheets xlSheets = xlBook.Worksheets();
			//
			int item = xlSheets.Count();
			// 第一个工作表
			ExcelWorksheet xlSheet = xlSheets.Item(1);
			System.out.println(xlSheet.CodeName());
			ExcelRange xlRange = xlSheet.Cells();
			int t = xlSheet.Rows().Count();
			int h = xlSheet.Cells().Count();

			System.out.println(item + "--------------" + t + "------------" + h);
			int i;
			int j;
			for (j = 1; j <= 10; j++)
			{
				for (i = 1; i <= 5; i++)
				{
					System.out.print(xlRange.Item(j, i).Value());
					if (i < t)
					{
						System.out.print(",");
					}

				}
				System.out.println("");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			 rm.release();
		}
	}

	static void JCOMCreateExcel()
	{
		ReleaseManager rm = new ReleaseManager();
		try
		{
			System.out.println("EXCEL startup...");
			// if already started, open new window
			ExcelApplication excel = new ExcelApplication(rm);
			excel.Visible(true);

			// display any information
			System.out.println("Version=" + excel.Version());
			System.out.println("UserName=" + excel.UserName());
			System.out.println("Caption=" + excel.Caption());
			System.out.println("Value=" + excel.Value());

			ExcelWorkbooks xlBooks = excel.Workbooks();
			ExcelWorkbook xlBook = xlBooks.Add(); // create new book

			// enumurate all files
			System.out.println("set infomation of files in current directory to cell ...");
			ExcelWorksheets xlSheets = xlBook.Worksheets();
			ExcelWorksheet xlSheet = xlSheets.Item(1);
			ExcelRange xlRange = xlSheet.Cells();

			xlRange.Item(1, 1).Value("filename");
			xlRange.Item(1, 2).Value("size");
			xlRange.Item(1, 3).Value("last modified time");
			xlRange.Item(1, 4).Value("is directory");
			xlRange.Item(1, 5).Value("is file");
			xlRange.Item(1, 6).Value("can read");
			xlRange.Item(1, 7).Value("can write");

			File path = new File("D:/");
			String[] filenames = path.list();
			for (int i = 0; i < filenames.length; i++)
			{
				File file = new File(filenames[i]);
				System.out.println(file);
				xlRange.Item(i + 2, 1).Value(file.getName());
				xlRange.Item(i + 2, 2).Value((int) file.length());
				xlRange.Item(i + 2, 3).Value(new Date(file.lastModified()));
				xlRange.Item(i + 2, 4).Value(file.isDirectory() ? "Yes" : "No");
				xlRange.Item(i + 2, 5).Value(file.isFile() ? "Yes" : "No");
				xlRange.Item(i + 2, 6).Value(file.canRead() ? "Yes" : "No");
				xlRange.Item(i + 2, 7).Value(file.canWrite() ? "Yes" : "No");
			}

			char start = 'B';
			char end = (char) ((byte) start + filenames.length - 1);
			System.out.println("end=[" + end + "]");
			String expression = "=Sum(B2:" + String.valueOf(end) + "2)";
			System.out.println("expression=[" + expression + "]");
			System.out.println("embed equation, calculate sum of filesize: " + expression);
			xlRange.Item(1, filenames.length + 2).Value("sum");
			xlRange.Item(2, filenames.length + 2).Formula(expression);
			xlRange.Columns().AutoFit(); // fit columns

			// comment out, if print out.
			// output default printer.
			// System.out.println("print out...");
			// xlSheet.PrintOut();

			// comment out, if book save to file.
			// if no path, save to(My Documents)
			// System.out.println
			// ("save to file... (My Documents)\\testExcel.xls");
			xlBook.SaveAs("D:/testExcel.xls");

			xlBook.Close(false, null, false);
			excel.Quit();

			System.out.println("thank you .");
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			rm.release();
		}
	}
}

poi

package com.junl.scott;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelImportPoi
{
	public static void main(String[] args) throws Exception
	{
		File file = new File("D:/功能说明文档.xls");

		String[][] result = getData(file, 1);

		int rowLength = result.length;

		for (int i = 0; i < rowLength; i++)
		{
			for (int j = 0; j < result[i].length; j++)
			{
				System.out.print(result[i][j] + "\t\t");
			}

			System.out.println();
		}
	}

	/**
	 * 
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * 
	 * @param file
	 *            读取数据的源Excel
	 * 
	 * @param ignoreRows
	 *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * 
	 * @return 读出的Excel中数据的内容
	 * 
	 * @throws FileNotFoundException
	 * 
	 * @throws IOException
	 */

	public static String[][] getData(File file, int ignoreRows)

	throws FileNotFoundException, IOException
	{

		List<String[]> result = new ArrayList<String[]>();

		int rowSize = 0;

		BufferedInputStream in = new BufferedInputStream(new FileInputStream(

		file));

		// 打开HSSFWorkbook

		POIFSFileSystem fs = new POIFSFileSystem(in);

		HSSFWorkbook wb = new HSSFWorkbook(fs);

		HSSFCell cell = null;

		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++)
		{

			HSSFSheet st = wb.getSheetAt(sheetIndex);

			// 第一行为标题，不取

			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++)
			{

				HSSFRow row = st.getRow(rowIndex);

				if (row == null)
				{

					continue;

				}

				int tempRowSize = row.getLastCellNum() + 1;

				if (tempRowSize > rowSize)
				{
					rowSize = tempRowSize;
				}

				String[] values = new String[rowSize];

				Arrays.fill(values, "");

				boolean hasValue = false;

				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++)
				{

					String value = "";

					cell = row.getCell(columnIndex);

					if (cell != null)
					{

						// 注意：一定要设成这个，否则可能会出现乱码

						cell.setEncoding(HSSFCell.ENCODING_UTF_16);

						switch (cell.getCellType())
						{

							case HSSFCell.CELL_TYPE_STRING:

								value = cell.getStringCellValue();

								break;

							case HSSFCell.CELL_TYPE_NUMERIC:

								if (HSSFDateUtil.isCellDateFormatted(cell))
								{

									Date date = cell.getDateCellValue();

									if (date != null)
									{

										value = new SimpleDateFormat("yyyy-MM-dd")

										.format(date);

									}
									else
									{

										value = "";

									}

								}
								else
								{

									value = new DecimalFormat("0").format(cell

									.getNumericCellValue());

								}

								break;

							case HSSFCell.CELL_TYPE_FORMULA:

								// 导入时如果为公式生成的数据则无值

								if (!cell.getStringCellValue().equals(""))
								{

									value = cell.getStringCellValue();

								}
								else
								{

									value = cell.getNumericCellValue() + "";

								}

								break;

							case HSSFCell.CELL_TYPE_BLANK:

								break;

							case HSSFCell.CELL_TYPE_ERROR:

								value = "";

								break;

							case HSSFCell.CELL_TYPE_BOOLEAN:

								value = (cell.getBooleanCellValue() == true ? "Y"

								: "N");

								break;

							default:

								value = "";

						}

					}

					if (columnIndex == 0 && value.trim().equals(""))
					{

						break;

					}

					values[columnIndex] = rightTrim(value);

					hasValue = true;

				}

				if (hasValue)
				{

					result.add(values);

				}

			}

		}

		in.close();

		String[][] returnArray = new String[result.size()][rowSize];

		for (int i = 0; i < returnArray.length; i++)
		{

			returnArray[i] = (String[]) result.get(i);

		}

		return returnArray;

	}

	/**
	 * 
	 * 去掉字符串右边的空格
	 * 
	 * @param str
	 *            要处理的字符串
	 * 
	 * @return 处理后的字符串
	 */

	public static String rightTrim(String str)
	{

		if (str == null)
		{

			return "";

		}

		int length = str.length();

		for (int i = length - 1; i >= 0; i--)
		{

			if (str.charAt(i) != 0x20)
			{
				break;
			}

			length--;

		}

		return str.substring(0, length);

	}

}