java实现Excel的读写操作以及复制列
import .*;
import ;
import ;
import ;
import ;
import .*;
import ;
import ;
import ;
public class ExcelUtil {
//默认单元格内容为数字时格式
private static DecimalFormat df = new DecimalFormat("0");
// 默认单元格格式化日期字符串
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 格式化数字
private static DecimalFormat nf = new DecimalFormat("0.00");
public static ArrayList<ArrayList<Object>> readExcel(File file){
if(file == null){
return null;
}
if(().endsWith("xlsx")){
//处理ecxel2007
return readExcel2007(file);
}else{
//处理ecxel2003
return readExcel2003(file);
}
}
/*
* @return 将返回结果存储在ArrayList内，存储结构与二位数组类似
* (0).get(0)表示过去Excel中0行0列单元格
*/
public static ArrayList<ArrayList<Object>> readExcel2003(File file){
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = (0);
HSSFRow row;
HSSFCell cell;
Object value;
for(int i = () , rowCount = 0; rowCount < () ; i++ ){
row = (i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != ()){//判断是否是最后一行
(colList);
}
continue;
}else{
rowCount++;
}
for( int j = () ; j <= () ;j++){
cell = (j);
if(cell == null || () == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != ()){//判断是否是该行中最后一个单元格
("");
}
continue;
}
switch(()){
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = ();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(().getDataFormatString())) {
value = df.format(());
} else if ("General".equals(()
.getDataFormatString())) {
value = nf.format(());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
System.out.println(i + "行" + j
+ " 列 is Number type ; DateFormt:"
+ ());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(());
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = ();
}// end switch
(value);
}//end for j
(colList);
}//end for i
return rowList;
}catch(Exception e){
return null;
}
}
public static ArrayList<ArrayList<Object>> readExcel2007(File file){
try{
ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
ArrayList<Object> colList;
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = (0);
XSSFRow row;
XSSFCell cell;
Object value;
for(int i = () , rowCount = 0; rowCount < () ; i++ ){
row = (i);
colList = new ArrayList<Object>();
if(row == null){
//当读取行为空时
if(i != ()){//判断是否是最后一行
(colList);
}
continue;
}else{
rowCount++;
}
for( int j = () ; j <= () ;j++){
cell = (j);
if(cell == null || () == HSSFCell.CELL_TYPE_BLANK){
//当该单元格为空
if(j != ()){//判断是否是该行中最后一个单元格
("");
}
continue;
}
switch(()){
case XSSFCell.CELL_TYPE_STRING:
System.out.println(i + "行" + j + " 列 is String type");
value = ();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(().getDataFormatString())) {
value = df.format(());
} else if ("General".equals(()
.getDataFormatString())) {
value = nf.format(());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell
.getNumericCellValue()));
}
System.out.println(i + "行" + j
+ " 列 is Number type ; DateFormt:"
+ ());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.println(i + "行" + j + " 列 is Boolean type");
value = Boolean.valueOf(());
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.println(i + "行" + j + " 列 is Blank type");
value = "";
break;
default:
System.out.println(i + "行" + j + " 列 is default type");
value = ();
}// end switch
(value);
}//end for j
(colList);
}//end for i
return rowList;
}catch(Exception e){
System.out.println("exception");
return null;
}
}
public static void writeExcel(ArrayList<ArrayList<Object>> result, String path) {
if (result == null) {
return;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = ("sheet1");
for (int i = 0; i < (); i++) {
HSSFRow row = (i);
if ((i) != null) {
for (int j = 0; j < (i).size(); j++) {
HSSFCell cell = (j);
((i).get(j).toString());
// if (i > 0 && j == 3) {//第二列复制到第四列
// ((i).get(1).toString());
// } else {
// ((i).get(j).toString());
// }
}
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
(os);
} catch (IOException e) {
();
}
byte[] content = ();
File file = new File(path);//Excel文件生成后存储的位置。
OutputStream fos = null;
try {
fos = new FileOutputStream(file);
(content);
();
();
} catch (Exception e) {
();
}
}
public static DecimalFormat getDf() {
return df;
}
public static void setDf(DecimalFormat df) {
ExcelUtil.df = df;
}
public static SimpleDateFormat getSdf() {
return sdf;
}
public static void setSdf(SimpleDateFormat sdf) {
ExcelUtil.sdf = sdf;
}
public static DecimalFormat getNf() {
return nf;
}
public static void setNf(DecimalFormat nf) {
ExcelUtil.nf = nf;
}
public static void main(String[] args) {
File file = new File("D:/");
ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file);
for(int i = 0 ;i < () ;i++){
for(int j = 0;j<(i).size(); j++){
System.out.println(i+"行 "+j+"列 "+ (i).get(j).toString());
}
}
ExcelUtil.writeExcel(result,"D:/");
}
}