Last Post : http://bbs.csdn.net/topics/390358967
The sharing an update after a utility class :
added some features do not find bug, if there are problems let me know , I went to change :
First , jar package:
the following code :
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
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 ExeclHelper {
/*该方法实现的功能是读,读的时候全部以String的方式存储,如果小数点后面为0的全部忽略,*/
public static String[][] poiReader(String filepath,File file) throws Exception{
String[][] result;
InputStream is = null;
if(!filepath.equals("")){
//输入输出流
is = new FileInputStream(filepath);
}else if(file.exists()){
is = new FileInputStream(file);
}else{
System.out.println("输入的路径并且文件为空");
}
//创建工作空间
Workbook 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);
//获取第i行的工作行的第6个单元格的值
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(".")){
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 poiWrite(String[][] str,String filepath){
InputStream inp;
ifexist(filepath);
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);
//设置值
cell.setCellValue(str[i][j]);
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(filepath);
wb.write(fileOut);
fileOut.close();
inp.close();
System.out.println("写入完成");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return filepath;
}
/*传入文件的地址,判断文件是否存在,如果不存在的话创建该文件*/
/*这个功能好像还存在一个小BUG,直接createNewFile();的文件不能用,以后找方法解决。*/
public static void ifexist(String path){
try {
File file=new File(path);
if(!file.exists()){
System.out.println("文件不存在,创建该文件,文件地址为:"+path);
file.createNewFile();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
------ Solution ------------------------------------- -------
me Bangding a chant
------ Solution ------------------------ --------------------
top one, learn .
------ Solution ---------------------------------------- ----
CSDN revision, the code displays but not easy to look good before ..
------ Solution ------------------ --------------------------
top that support it, access points , hee hee
------ Solution --------------------------------------------
------ Solution ---- ----------------------------------------
share to those who are unwilling official website English people do not want to see come good.
Access points
------ Solution ---------------------------------- ----------
taught , and learn under
------ Solution --------------------- -----------------------
I have to stroll , java & android developers expect more big group 246,626,189 cattle join together to discuss technical .
------ For reference only ---------------------------------------
I collapsed. Changed three times or part of the code is commented . . .
code itself is no problem , will forward to see.
changed or withheld help .
------ For reference only -------------------------------------- -
------ For reference only ---------------------------------- -----
has found a BUG, local file has been updated, that is, if the cell is: is priced at 7.9 yuan , so that will be incorrect , the regular match on the line , next Monday time to work on updating .
------ For reference only -------------------------------------- -
In fact, I was kind of person.
------ For reference only -------------------------------------- -
------ For reference only ---------------------------------- -----
lz, solve ah
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");
}
}
The xsl data read out, and then deposited into e: \ \ a.xls when being given .
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)
This sentence error : Workbook wb = WorkbookFactory.create (inp);
directory structure : img = http://img.bbs.csdn.net/upload/201305/15/1368586899_312225.gif] [/ img]
------ For reference only --- ------------------------------------
Reply cite before this error because you just used my tools generated a.xls
, and I directly generate xls files that are problematic ( new out files directly csv format , of course, can not be deposited excel file ) .
In fact, I own this utility class already changed locally .
with the new bar.
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);
}
}
------ For reference only ----------------------------------- ----
landlord of the code is a big problem ah , and I can not test the next school EXCEL
columnnum = sheet.getRow (0). getLastCellNum (); This line of course, I would have failed the first line of the EXCEL table is empty,
java.lang.NullPointerException
at excel.ExcelHelper.poiReader (ExcelHelper.java: 55)
at excel.ReadExcelTest.main (ReadExcelTest.java: 10) reported the following error
------ For reference only --------------- ------------------------
upstairs code only the first column on sheet1 copying and the first column empty lies above error Khan died
------ For reference only --------------------- ------------------
know how wrong you know how to change it.
completely avoid errors is impossible, I can do is go after the wrong patch BUG
For an empty solution is also very simple , but the format was changed to require the return List [] form ,
没有评论:
发表评论