概述
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] + "tt");
}
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);
}
}
最后
以上就是迷你吐司为你收集整理的java 使用工具读取Excel文件的全部内容,希望文章能够帮你解决java 使用工具读取Excel文件所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复