jar包
dom4j-1.6.1.jar
geronimo-stax-api_1.0_spec-1.0.jar
ooxml-schemas-1.0.jar
poi-3.7-jdk1.4-20110508-rc2.jar
poi-3.7-jdk1.4-ooxml-20110508-rc2.jar
poi-3.7-jdk1.4-scratchpad-20110508-rc2.jar
xbean.jar
xmlbeans-qname.jar
package com.testofdulei;
import java.io.*;
import java.util.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.ibm.bisc.ebiz.base.*;
public class ReadExcel
{
private static final String path = "/projects/test/";//上传文件地址
public static void main(String[] args) {
File file = new File("C:\\ExcelDemo.xlsx");
try {
readExcel(file);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 读取 excel 2003方法
* @param file
* @return
* @throws IOException
*/
private static String[][] read2003Excel(File file) throws IOException
{
System.out.println("----------进入read2003Excel方法---------------");
List result = new ArrayList();
int rowSize = 0;
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
XSSFWorkbook hwb2007 = new XSSFWorkbook();
System.out.println("=============== 此 " + file.getName() + " 文档一共有 " + hwb.getNumberOfSheets() + " 列..." + hwb.getSheetAt(0).getLastRowNum() + " 行... ");
Cell cell = null;
XSSFCell cell2007 = null;
for (int i = 0; i < hwb.getNumberOfSheets(); i++) //循环列
{
Sheet sheet = (Sheet) hwb.getSheetAt(i);
XSSFSheet sheet2007 = (XSSFSheet) hwb2007.createSheet();
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)// 循环行 不包括标题
{
XSSFRow row2007 = (XSSFRow) sheet2007.createRow(rowIndex);
HSSFRow row = (HSSFRow) sheet.getRow(rowIndex);//得到每一行数据
if (row == null)
{
continue;
}
int tempRowSize = row.getLastCellNum();
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 = (Cell) row.getCell(columnIndex);
cell2007 = (XSSFCell) row2007.createCell(columnIndex);
if (cell == null)
{
continue;
}
switch (cell.getCellType())
{
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值 String
break;
case XSSFCell.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());
}
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值 double
break;
case XSSFCell.CELL_TYPE_FORMULA:
if (!cell.getStringCellValue().equals(""))
{
value = cell.getStringCellValue();
} else
{
value = cell.getNumericCellValue() + "";
}
row2007.getCell(columnIndex).setCellValue(value);
;//创建并设置单元格值 String
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y" : "N");
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值 boolean
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值
break;
default:
value = cell.toString();
row2007.getCell(columnIndex).setCellValue(value);//创建并设置单元格值
}
if (columnIndex == 0 && "".equals(value.trim()))
{
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue)
{
result.add(values);
}
}
}
try
{
FileOutputStream fileOut = new FileOutputStream(path + file.getName() + "x");//excel 2003文件写入excel 2007模型
System.out.println("-----------------------得到文件名称--------------------------" + file.getName());
hwb2007.write(fileOut);
System.out.println("-----------------------写文件操作完成--------------------------");
fileOut.flush();
fileOut.close();
} catch (Exception e)
{
e.printStackTrace();
}
String[][] returnArray = new String[result.size()][rowSize];//返回打印字符串
for (int m = 0; m < returnArray.length; m++)
{
returnArray[m] = (String[]) result.get(m);
}
return returnArray;
}
/**
* 读取excel 2007方法
* @param file
* @return
* @throws IOException
*/
private static String[][] read2007Excel(File file) throws IOException
{
System.out.println("----------进入read2007Excel方法---------------");
List result = new ArrayList();
int rowSize = 0;
XSSFCell cell = null;
HSSFCell cell2003 = null;
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
System.out.println("=============== 此 " + file.getName() + " 文档一共有 " + xwb.getNumberOfSheets() + " 列..." + xwb.getSheetAt(0).getLastRowNum() + " 行... ");
HSSFWorkbook xwb2003 = new HSSFWorkbook();
System.out.println("----------创建工作薄,strPath传入文件路径:---------------" + xwb);
for (int sheetIndex = 0; sheetIndex < xwb.getNumberOfSheets(); sheetIndex++)//循环列
{
XSSFSheet sheet = (XSSFSheet) xwb.getSheetAt(sheetIndex);
HSSFSheet sheet2003 = (HSSFSheet) xwb2003.createSheet();
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)//循环行
{
HSSFRow row2003 = (HSSFRow) sheet2003.createRow(rowIndex);
XSSFRow row = (XSSFRow) sheet.getRow(rowIndex);//得到每一行数据
if (row == null)
{
continue;
}
int tempRowSize = row.getLastCellNum();
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 = (XSSFCell) row.getCell(columnIndex);//得到的每行每个具体值
cell2003 = (HSSFCell) row2003.createCell(columnIndex);
if (cell != null)
{
switch (cell.getCellType())
{
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
row2003.getCell(columnIndex).setCellValue(value);
break;
case XSSFCell.CELL_TYPE_NUMERIC:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if ("General".equals(cell.getCellStyle().getDataFormatString()))
{
value = new DecimalFormat("0").format(cell.getNumericCellValue());
} else
{
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
row2003.getCell(columnIndex).setCellValue(value);
break;
case XSSFCell.CELL_TYPE_FORMULA:
if (!cell.getStringCellValue().equals(""))
{
value = cell.getStringCellValue();
} else
{
value = cell.getNumericCellValue() + "";
}
row2003.getCell(columnIndex).setCellValue(value);
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y" : "N");
row2003.getCell(columnIndex).setCellValue(value);
break;
default:
value = "";
break;
}
}
if (columnIndex == 0 && value.trim().equals(""))
{
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue)
{
result.add(values);
}
}
}
try
{
String fileName = file.getName();
System.out.println("-----------------------得到文件名称--------------------------" + fileName);
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);//得到文件类型
System.out.println("-----------------------得到文件类型------------------------------------------" + extension);
FileOutputStream fileOut = new FileOutputStream(path + fileName.substring(0, fileName.length() - 1));//excel 2007文件写入excel 2003模型
System.out.println("-----------------------创建文件地址完成!------------------------------------");
xwb2003.write(fileOut);
System.out.println("-----------------------写文件操作完成!---------------------------------------");
fileOut.flush();
fileOut.close();
} catch (Exception e)
{
e.printStackTrace();
}
String[][] returnArray = new String[result.size()][rowSize];//返回打印字符串
for (int i = 0; i < returnArray.length; i++)
{
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
/**
* 对外提供读取excel方法
* @param file
* @return
* @throws IOException
*/
public static List readExcel(File file) throws IOException
{
String fileName = file.getName();
List list = new ArrayList();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
System.out.println("得到文件类型:" + extension);
String[][] result = null;
if ("xls".equals(extension))
{
result = read2003Excel(file);
} else if ("xlsx".equals(extension))
{
result = read2007Excel(file);
} else
{
throw new IOException("");
}
int rowLength = result.length;
for (int i = 0; i < rowLength; i++)
{
StringBuffer sb = new StringBuffer();
for (int j = 0; j < result[i].length; j++)
{
if (!"".equals(result[i][j]) && result[i][j].trim().length() > 0)
{
sb.append(result[i][j]).append("##");
} else
{
sb.append("@@").append("##");
}
}
if (sb.toString().endsWith("##"))
{
sb.delete(sb.toString().length() - 2, sb.toString().length());
}
System.out.println(sb.toString());
list.add(sb.toString());
}
return list;
}
/**
* 去掉右边字符串空格
* @param value
* @return
*/
public static String rightTrim(String value)
{
if (value == null)
{
return "";
}
int length = value.length();
for (int i = length - 1; i >= 0; i--)
{
if (value.charAt(i) != 0x20)
{
break;
}
length--;
}
return value.substring(0, length);
}
}