2013年9月17日星期二

Share a changed Execll tools ( last upload a bug) + scattered points

 This post last edited by the AA5279AA on 2013-01-30 12:37:41
First hold an apology , read Execl last upload POI problem with the way the last line of execl not read , this is my problem with the code . .
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 -------------------------------------- -
  The reply deleted by an administrator at 2013-01-31 08:40:53

------ 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 -------------------------------------- -
  The reply deleted by an administrator at 2013-02-03 08:41:46

------ 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 ,

没有评论:

发表评论