lz,求解啊 public class MainTest { public static void main(String[] args) throws Exception { ExcelHelper eh = new ExcelHelper(); String[][] data = eh.poiReader("E:\\资料\\个人周报20130118.xls", null); for(int i = 0; i < data.length; i++){ for(int j = 0; j < data[i].length; j++){ System.out.print(data[i][j]+"\t"); } System.out.println(); } eh.poiWrite(data, "e:\\a.xls"); } } 将xsl中的数据读出来了,然后存入到e:\\a.xls时报错了。 java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:65) at com.test.ExcelHelper.poiWrite(ExcelHelper.java:85) at com.test.MainTest.main(MainTest.java:13)这句话报错:Workbook wb = WorkbookFactory.create(inp); 目录结构:img=http://img.bbs.csdn.net/upload/201305/15/1368586899_312225.gif][/img]
回复之前引用一下,这个报错原因是你直接用了我的工具类生成的a.xls ,而我那个直接生成xls文件是有问题的(直接new出来的文件时csv格式的,当然不能存入excel文件)。 这个工具类其实我自己早就在本地改了。 用新的吧。package cn.helper;import java.io.File; 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.util.HashMap; import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory;/* * Date:2013年1月30日11:39:36 * Author:leilei */ //该类是一个工具类,实现的功能是对execl文件简单读和存的功能 public class ExcelHelper { // 该方法实现的功能是读,读的时候全部以String的方式存储,如果小数点后面为0的全部忽略, public static String[][] poiReader(String filepath, File file) { String[][] result = null; InputStream is = null; try { if (filepath != null && !filepath.equals("")) { // 输入输出流 is = new FileInputStream(filepath); } else if (file.exists()) { is = new FileInputStream(file); } else { System.out.println("输入的路径和文件为空"); } // 创建工作空间 Workbook wb = null; wb = WorkbookFactory.create(is); // 获取工作表 Sheet sheet = wb.getSheetAt(0);// 获取第一个工作表 // 工作行 Row row; // 工作单元格 Cell cell = null; int rownum;// 行 int columnnum;// 列 rownum = sheet.getLastRowNum() + 1; columnnum = sheet.getRow(0).getLastCellNum(); // 实例化返回的数组对象 result = new String[rownum][columnnum]; System.out.println("rownum:" + rownum); System.out.println("columnnum:" + columnnum); for (int i = 0; i < rownum; i++) { row = sheet.getRow(i); // 该行为空的话跳过,不加入数组 if (row == null) continue; for (int j = 0; j < columnnum; j++) { cell = row.getCell(j); // 该单元格为空的话,设为空 if (cell == null) { result[i][j] = ""; } else { String str = cell.toString(); if (str.contains(".") && str.matches("[0-9]{1,}.[0-9]{1,}")) { String[] s = str.split("\\."); if (Integer.parseInt(s[1]) == 0) { result[i][j] = s[0]; } else { result[i][j] = str; } } else { result[i][j] = str; } } } } } catch (Exception e) { e.printStackTrace(); } return result; } // 该方法实现的功能是读,读的时候全部以String的方式存储,如果小数点后面为0的全部忽略, public static String[][] poiReader(String filepath, File file, int k) { String[][] result; InputStream is = null; try { if (filepath != null && !filepath.equals("")) { // 输入输出流 is = new FileInputStream(filepath); } else if (file.exists()) { is = new FileInputStream(file); } else { System.out.println("输入的路径并且文件为空"); } } catch (FileNotFoundException e) { e.printStackTrace(); } // 创建工作空间 Workbook wb = null; try { wb = WorkbookFactory.create(is); } catch (Exception e) { e.printStackTrace(); } // 获取工作表 Sheet sheet = wb.getSheetAt(k);// 获取第一个工作表 // 工作行 Row row; // 工作单元格 Cell cell = null; int rownum;// 行 int columnnum;// 列 rownum = sheet.getLastRowNum() + 1; columnnum = sheet.getRow(0).getLastCellNum(); // 实例化返回的数组对象 result = new String[rownum][columnnum]; System.out.println("rownum:" + rownum); System.out.println("columnnum:" + columnnum); for (int i = 0; i < rownum; i++) { row = sheet.getRow(i); for (int j = 0; j < columnnum; j++) { cell = row.getCell(j); if (cell == null) { String str = ""; result[i][j] = str; } else { String str = cell.toString(); if (str.contains(".") && str.matches("[0-9]{1,}.[0-9]{1,}")) { String[] s = str.split("\\."); if (Integer.parseInt(s[1]) == 0) { result[i][j] = s[0]; } else { result[i][j] = str; } } else { result[i][j] = str; } } } } return result; } // 输入二维数组和文件存放的地址,该方法把数组中的内容(包括表头)存入execl文件,返回文件存放地址 // 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。 public static String poiWriteWithoutFile(String[][] str, String filepath) { try { if (str == null) { return ""; } HSSFWorkbook wb = new HSSFWorkbook(); int rownum = str.length; int columnum = str[0].length; Sheet sheet = wb.createSheet(); for (int i = 0; i < rownum; i++) { // System.out.println("i:"+i); Row row = sheet.createRow(i); for (int j = 0; j < columnum; j++) { /* System.out.println("j:"+j); */ Cell cell = row.createCell(j); // 设置格式 cell.setCellType(Cell.CELL_TYPE_STRING); // 设置值 str[i][j] = (str[i][j] == null) ? "" : str[i][j]; cell.setCellValue(str[i][j]); } } FileOutputStream out = new FileOutputStream(filepath); wb.write(out); } catch (IOException e) { e.printStackTrace(); } return filepath; } // 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。 public static String poiWrite(String[][] str, String filepath) { InputStream inp; try { inp = new FileInputStream(filepath); int rownum = str.length; int columnum = str[0].length; Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); for (int i = 0; i < rownum; i++) { // System.out.println("i:"+i); Row row = sheet.createRow(i); for (int j = 0; j < columnum; j++) { /* System.out.println("j:"+j); */ Cell cell = row.createCell(j); // 设置格式 cell.setCellType(Cell.CELL_TYPE_STRING); // 设置值 if (str[i][j] != null) { cell.setCellValue(str[i][j]); } else { cell.setCellValue(""); } } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(filepath); wb.write(fileOut); fileOut.close(); inp.close(); System.out.println("写入完成,路径为" + filepath); } catch (Exception e) { e.printStackTrace(); } return filepath; } // 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。 public static String poiWrite(String[][] str, String filepath,int k) { InputStream inp; try { inp = new FileInputStream(filepath); int rownum = str.length; int columnum = str[0].length; Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(k); for (int i = 0; i < rownum; i++) { // System.out.println("i:"+i); Row row = sheet.createRow(i); for (int j = 0; j < columnum; j++) { /* System.out.println("j:"+j); */ Cell cell = row.createCell(j); // 设置格式 cell.setCellType(Cell.CELL_TYPE_STRING); // 设置值 if(str[i][j]!=null){ cell.setCellValue(str[i][j]); }else{ cell.setCellValue(""); } } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(filepath); wb.write(fileOut); fileOut.close(); inp.close(); System.out.println("写入完成,路径为" + filepath); } catch (Exception e) { e.printStackTrace(); } return filepath; }
public static void poiWriteOut(String[][] str,OutputStream out){ if (str == null) { return; } HSSFWorkbook wb=new HSSFWorkbook(); int rownum = str.length; int columnum = str[0].length; Sheet sheet = wb.createSheet(); for (int i = 0; i < rownum; i++) { // System.out.println("i:"+i); Row row = sheet.createRow(i); for (int j = 0; j < columnum; j++) { /* System.out.println("j:"+j); */ Cell cell = row.createCell(j); // 设置格式 cell.setCellType(Cell.CELL_TYPE_STRING); // 设置值 str[i][j]=(str[i][j]==null)?"":str[i][j]; cell.setCellValue(str[i][j]); } } try { wb.write(out); } catch (IOException e) { e.printStackTrace(); } } // 读取execl转换成map public static Map<String, String> execlToMap(String path,File file,int keyline,int valueline) { Map<String, String> map = new HashMap<String, String>(); String[][] s; if(file==null){ s = ExcelHelper.poiReader(path, null); }else{ s= ExcelHelper.poiReader("", file); } for (int i = 0; i < s.length; i++) { for (int j = 0; j < s[0].length; j++) { if (map.get(s[i][keyline]) == null) { map.put(s[i][keyline], s[i][valueline]); } else { String key = s[i][keyline]; String value = map.get(key); if (value.equals(s[i][valueline])) { continue; } else { map.put(key, value + "," + s[i][valueline]); } } } } return map; } public static void mapToExecl(String path,Map<String,String> map){ String[][] str=new String[map.size()][2]; int i=0; for(String key:map.keySet()){ str[i][0]=key; str[i++][1]=map.get(key); } poiWrite(str, path); } }
楼主的代码问题很大啊,我随便测试下 一个EXCEL 就读不到 columnnum = sheet.getRow(0).getLastCellNum(); 这行代码就失败了 当然我EXCEL表第一行是空的, java.lang.NullPointerException at excel.ExcelHelper.poiReader(ExcelHelper.java:55) at excel.ReadExcelTest.main(ReadExcelTest.java:10)报了如下的错误
public class MainTest {
public static void main(String[] args) throws Exception {
ExcelHelper eh = new ExcelHelper();
String[][] data = eh.poiReader("E:\\资料\\个人周报20130118.xls", null);
for(int i = 0; i < data.length; i++){
for(int j = 0; j < data[i].length; j++){
System.out.print(data[i][j]+"\t");
}
System.out.println();
}
eh.poiWrite(data, "e:\\a.xls");
}
}
将xsl中的数据读出来了,然后存入到e:\\a.xls时报错了。
java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:65)
at com.test.ExcelHelper.poiWrite(ExcelHelper.java:85)
at com.test.MainTest.main(MainTest.java:13)这句话报错:Workbook wb = WorkbookFactory.create(inp);
目录结构:img=http://img.bbs.csdn.net/upload/201305/15/1368586899_312225.gif][/img]
回复之前引用一下,这个报错原因是你直接用了我的工具类生成的a.xls
,而我那个直接生成xls文件是有问题的(直接new出来的文件时csv格式的,当然不能存入excel文件)。
这个工具类其实我自己早就在本地改了。
用新的吧。package cn.helper;import java.io.File;
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.util.HashMap;
import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;/*
* Date:2013年1月30日11:39:36
* Author:leilei
*/
//该类是一个工具类,实现的功能是对execl文件简单读和存的功能
public class ExcelHelper {
// 该方法实现的功能是读,读的时候全部以String的方式存储,如果小数点后面为0的全部忽略,
public static String[][] poiReader(String filepath, File file) {
String[][] result = null;
InputStream is = null;
try {
if (filepath != null && !filepath.equals("")) {
// 输入输出流
is = new FileInputStream(filepath);
} else if (file.exists()) {
is = new FileInputStream(file); } else {
System.out.println("输入的路径和文件为空");
} // 创建工作空间
Workbook wb = null; wb = WorkbookFactory.create(is); // 获取工作表
Sheet sheet = wb.getSheetAt(0);// 获取第一个工作表
// 工作行
Row row;
// 工作单元格
Cell cell = null;
int rownum;// 行
int columnnum;// 列
rownum = sheet.getLastRowNum() + 1;
columnnum = sheet.getRow(0).getLastCellNum();
// 实例化返回的数组对象
result = new String[rownum][columnnum]; System.out.println("rownum:" + rownum);
System.out.println("columnnum:" + columnnum); for (int i = 0; i < rownum; i++) {
row = sheet.getRow(i);
// 该行为空的话跳过,不加入数组
if (row == null)
continue;
for (int j = 0; j < columnnum; j++) {
cell = row.getCell(j);
// 该单元格为空的话,设为空
if (cell == null) {
result[i][j] = "";
} else {
String str = cell.toString();
if (str.contains(".")
&& str.matches("[0-9]{1,}.[0-9]{1,}")) {
String[] s = str.split("\\.");
if (Integer.parseInt(s[1]) == 0) {
result[i][j] = s[0];
} else {
result[i][j] = str;
}
} else {
result[i][j] = str;
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
// 该方法实现的功能是读,读的时候全部以String的方式存储,如果小数点后面为0的全部忽略,
public static String[][] poiReader(String filepath, File file, int k) {
String[][] result;
InputStream is = null;
try {
if (filepath != null && !filepath.equals("")) {
// 输入输出流
is = new FileInputStream(filepath);
} else if (file.exists()) {
is = new FileInputStream(file); } else {
System.out.println("输入的路径并且文件为空");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
// 创建工作空间
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
} catch (Exception e) {
e.printStackTrace();
}
// 获取工作表
Sheet sheet = wb.getSheetAt(k);// 获取第一个工作表
// 工作行
Row row;
// 工作单元格
Cell cell = null;
int rownum;// 行
int columnnum;// 列
rownum = sheet.getLastRowNum() + 1;
columnnum = sheet.getRow(0).getLastCellNum();
// 实例化返回的数组对象
result = new String[rownum][columnnum]; System.out.println("rownum:" + rownum);
System.out.println("columnnum:" + columnnum);
for (int i = 0; i < rownum; i++) {
row = sheet.getRow(i);
for (int j = 0; j < columnnum; j++) {
cell = row.getCell(j);
if (cell == null) {
String str = "";
result[i][j] = str;
} else {
String str = cell.toString();
if (str.contains(".") && str.matches("[0-9]{1,}.[0-9]{1,}")) {
String[] s = str.split("\\.");
if (Integer.parseInt(s[1]) == 0) {
result[i][j] = s[0];
} else {
result[i][j] = str;
}
} else {
result[i][j] = str;
}
}
}
}
return result;
} // 输入二维数组和文件存放的地址,该方法把数组中的内容(包括表头)存入execl文件,返回文件存放地址
// 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。
public static String poiWriteWithoutFile(String[][] str, String filepath) {
try {
if (str == null) {
return "";
}
HSSFWorkbook wb = new HSSFWorkbook();
int rownum = str.length;
int columnum = str[0].length;
Sheet sheet = wb.createSheet();
for (int i = 0; i < rownum; i++) {
// System.out.println("i:"+i);
Row row = sheet.createRow(i);
for (int j = 0; j < columnum; j++) {
/* System.out.println("j:"+j); */
Cell cell = row.createCell(j);
// 设置格式
cell.setCellType(Cell.CELL_TYPE_STRING);
// 设置值
str[i][j] = (str[i][j] == null) ? "" : str[i][j];
cell.setCellValue(str[i][j]);
}
}
FileOutputStream out = new FileOutputStream(filepath);
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
}
return filepath;
} // 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。
public static String poiWrite(String[][] str, String filepath) {
InputStream inp;
try {
inp = new FileInputStream(filepath);
int rownum = str.length;
int columnum = str[0].length; Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
for (int i = 0; i < rownum; i++) {
// System.out.println("i:"+i);
Row row = sheet.createRow(i);
for (int j = 0; j < columnum; j++) {
/* System.out.println("j:"+j); */
Cell cell = row.createCell(j);
// 设置格式
cell.setCellType(Cell.CELL_TYPE_STRING);
// 设置值
if (str[i][j] != null) {
cell.setCellValue(str[i][j]);
} else {
cell.setCellValue("");
}
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(filepath);
wb.write(fileOut);
fileOut.close();
inp.close();
System.out.println("写入完成,路径为" + filepath);
} catch (Exception e) {
e.printStackTrace();
}
return filepath;
}
// 注:方法本身没有判断文件是否属于execl文件的功能,存的时候不附带任何格式。
public static String poiWrite(String[][] str, String filepath,int k) {
InputStream inp;
try {
inp = new FileInputStream(filepath);
int rownum = str.length;
int columnum = str[0].length; Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(k);
for (int i = 0; i < rownum; i++) {
// System.out.println("i:"+i);
Row row = sheet.createRow(i);
for (int j = 0; j < columnum; j++) {
/* System.out.println("j:"+j); */
Cell cell = row.createCell(j);
// 设置格式
cell.setCellType(Cell.CELL_TYPE_STRING);
// 设置值
if(str[i][j]!=null){
cell.setCellValue(str[i][j]);
}else{
cell.setCellValue("");
}
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(filepath);
wb.write(fileOut);
fileOut.close();
inp.close();
System.out.println("写入完成,路径为" + filepath);
} catch (Exception e) {
e.printStackTrace();
}
return filepath;
}
public static void poiWriteOut(String[][] str,OutputStream out){
if (str == null) {
return;
}
HSSFWorkbook wb=new HSSFWorkbook();
int rownum = str.length;
int columnum = str[0].length;
Sheet sheet = wb.createSheet();
for (int i = 0; i < rownum; i++) {
// System.out.println("i:"+i);
Row row = sheet.createRow(i);
for (int j = 0; j < columnum; j++) {
/* System.out.println("j:"+j); */
Cell cell = row.createCell(j);
// 设置格式
cell.setCellType(Cell.CELL_TYPE_STRING);
// 设置值
str[i][j]=(str[i][j]==null)?"":str[i][j];
cell.setCellValue(str[i][j]);
}
}
try {
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
// 读取execl转换成map
public static Map<String, String> execlToMap(String path,File file,int keyline,int valueline) {
Map<String, String> map = new HashMap<String, String>();
String[][] s;
if(file==null){
s = ExcelHelper.poiReader(path, null);
}else{
s= ExcelHelper.poiReader("", file);
}
for (int i = 0; i < s.length; i++) {
for (int j = 0; j < s[0].length; j++) {
if (map.get(s[i][keyline]) == null) {
map.put(s[i][keyline], s[i][valueline]);
} else {
String key = s[i][keyline];
String value = map.get(key);
if (value.equals(s[i][valueline])) {
continue;
} else {
map.put(key, value + "," + s[i][valueline]);
}
}
}
}
return map;
}
public static void mapToExecl(String path,Map<String,String> map){
String[][] str=new String[map.size()][2];
int i=0;
for(String key:map.keySet()){
str[i][0]=key;
str[i++][1]=map.get(key);
}
poiWrite(str, path);
}
}
columnnum = sheet.getRow(0).getLastCellNum(); 这行代码就失败了 当然我EXCEL表第一行是空的,
java.lang.NullPointerException
at excel.ExcelHelper.poiReader(ExcelHelper.java:55)
at excel.ReadExcelTest.main(ReadExcelTest.java:10)报了如下的错误
完全的避免错误是不可能的,我能做的只是出了错之后去修补BUG
对于为空的解决方法也很简单,只不过得需要把返回的格式改成List[]的形式、
完全的避免错误是不可能的,我能做的只是出了错之后去修补BUG
对于为空的解决方法也很简单,只不过得需要把返回的格式改成List[]的形式、我是本着学习来的