java编写excel代码_java写入和写出EXCEL（含源代码）

importjava.awt.List;importjava.io.ByteArrayOutputStream;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;public classExcelRead {

String path;publicString getPath() {returnpath;

}public voidsetPath(String path) {this.path =path;

}//默认单元格内容为数字时格式

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>readExcel(File file){if(file == null){return null;

}if(file.getName().endsWith("xlsx")){//处理ecxel2007

returnreadExcel2007(file);

}else{//处理ecxel2003

returnreadExcel2003(file);

}

}/** @return 将返回结果存储在ArrayList内，存储结构与二位数组类似

* lists.get(0).get(0)表示过去Excel中0行0列单元格*/

public static ArrayList>readExcel2003(File file){try{

ArrayList> rowList = new ArrayList>();

ArrayListcolList;

HSSFWorkbook wb= new HSSFWorkbook(newFileInputStream(file));

HSSFSheet sheet= wb.getSheetAt(0);

HSSFRow row;

HSSFCell cell;

Object value;for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++){

row=sheet.getRow(i);

colList= new ArrayList();if(row == null){//当读取行为空时

if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行

rowList.add(colList);

}continue;

}else{

rowCount++;

}for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){

cell=row.getCell(j);if(cell == null || cell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){//当该单元格为空

if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格

colList.add("");

}continue;

}switch(cell.getCellType()){caseXSSFCell.CELL_TYPE_STRING://System.out.println(i + "行" + j + " 列 is String type");

value =cell.getStringCellValue();break;caseXSSFCell.CELL_TYPE_NUMERIC:if ("@".equals(cell.getCellStyle().getDataFormatString())) {

value=df.format(cell.getNumericCellValue());

}else if ("General".equals(cell.getCellStyle()

.getDataFormatString())) {

value=nf.format(cell.getNumericCellValue());

}else{

value=sdf.format(HSSFDateUtil.getJavaDate(cell

.getNumericCellValue()));

}//System.out.println(i + "行" + j//+ " 列 is Number type ; DateFormt:"//+ value.toString());

break;caseXSSFCell.CELL_TYPE_BOOLEAN://System.out.println(i + "行" + j + " 列 is Boolean type");

value =Boolean.valueOf(cell.getBooleanCellValue());break;caseXSSFCell.CELL_TYPE_BLANK://System.out.println(i + "行" + j + " 列 is Blank type");

value = "";break;default://System.out.println(i + "行" + j + " 列 is default type");

value =cell.toString();

}//end switch

colList.add(value);

}//end for j

rowList.add(colList);

}//end for i

returnrowList;

}catch(Exception e){return null;

}

}public static ArrayList>readExcel2007(File file){try{

ArrayList> rowList = new ArrayList>();

ArrayListcolList;

XSSFWorkbook wb= new XSSFWorkbook(newFileInputStream(file));

XSSFSheet sheet= wb.getSheetAt(0);

XSSFRow row;

XSSFCell cell;

Object value;for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++){

row=sheet.getRow(i);

colList= new ArrayList();if(row == null){//当读取行为空时

if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行

rowList.add(colList);

}continue;

}else{

rowCount++;

}for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){

cell=row.getCell(j);if(cell == null || cell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){//当该单元格为空

if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格

colList.add("");

}continue;

}switch(cell.getCellType()){caseXSSFCell.CELL_TYPE_STRING://System.out.println(i + "行" + j + " 列 is String type");

value =cell.getStringCellValue();break;caseXSSFCell.CELL_TYPE_NUMERIC:if ("@".equals(cell.getCellStyle().getDataFormatString())) {

value=df.format(cell.getNumericCellValue());

}else if ("General".equals(cell.getCellStyle()

.getDataFormatString())) {

value=nf.format(cell.getNumericCellValue());

}else{

value=sdf.format(HSSFDateUtil.getJavaDate(cell

.getNumericCellValue()));

}//System.out.println(i + "行" + j//+ " 列 is Number type ; DateFormt:"//+ value.toString());

break;caseXSSFCell.CELL_TYPE_BOOLEAN://System.out.println(i + "行" + j + " 列 is Boolean type");

value =Boolean.valueOf(cell.getBooleanCellValue());break;caseXSSFCell.CELL_TYPE_BLANK://System.out.println(i + "行" + j + " 列 is Blank type");

value = "";break;default://System.out.println(i + "行" + j + " 列 is default type");

value =cell.toString();

}//end switch

colList.add(value);

}//end for j

rowList.add(colList);

}//end for i

returnrowList;

}catch(Exception e){

System.out.println("exception");return null;

}

}public staticArrayList getFiles(String filePath){

File root= newFile(filePath);

File[]files=root.listFiles();

ArrayList filelist= newArrayList();for(File file:files){if(file.isDirectory()){

filelist.addAll(getFiles(file.getAbsolutePath()));

}else{

String newpath=file.getAbsolutePath();if(newpath.contains("交易记录")){

filelist.add(newpath);

}

}

}returnfilelist;

}public voidreadBook(String path3) {

String filePath=path3;

ArrayList filelist=getFiles(filePath);

ArrayListresultAll = new ArrayList();for(int i = 0;i

String path=(String) filelist.get(i);

System.out.println(path);

ArrayListresult =Graph(path);

String[] path2= path.split("\\");int num = result.get(0).size();

ArrayList result2= newArrayList();for(int j = 0;j

result2.add(path2[path2.length-2]);

}

ArrayList result3= newArrayList();for(int j = 0;j

result3.add(path2[path2.length-3]);

}

result.add(result2);

result.add(result3);if(resultAll.size()==0){

resultAll=result;

}else{for(int j = 0;j

resultAll.get(j).add(result.get(j).get(k));

}

}

}

}

writeExcel(resultAll,"D:/a.xls");

}public static void writeExcel(ArrayListresult,String path){if(result == null){return;

}

HSSFWorkbook wb= newHSSFWorkbook();

HSSFSheet sheet= wb.createSheet("sheet1");for(int i = 0 ;i < result.get(0).size() ; i++){

HSSFRow row=sheet.createRow(i);for(int j = 0; j < result.size() ; j ++){

HSSFCell cell= row.createCell((short)j);

cell.setCellValue(result.get(j).get(i).toString());

}

}

ByteArrayOutputStream os= newByteArrayOutputStream();try{

wb.write(os);

}catch(IOException e){

e.printStackTrace();

}byte[] content =os.toByteArray();

File file= new File(path);//Excel文件生成后存储的位置。

OutputStream fos = null;try{

fos= newFileOutputStream(file);

wb.write(fos);

os.close();

fos.close();

}catch(Exception e){

e.printStackTrace();

}

}public staticDecimalFormat getDf() {returndf;

}public static voidsetDf(DecimalFormat df) {

ExcelRead.df=df;

}public staticSimpleDateFormat getSdf() {returnsdf;

}public static voidsetSdf(SimpleDateFormat sdf) {

ExcelRead.sdf=sdf;

}public staticDecimalFormat getNf() {returnnf;

}public static voidsetNf(DecimalFormat nf) {

ExcelRead.nf=nf;

}public static ArrayListGraph(String path){

File file= newFile(path);

ArrayList> result =ExcelRead.readExcel(file);

ArrayListprice = new ArrayList();//价格序列

ArrayListtime = new ArrayList();//时间序列

ArrayListbuyList = new ArrayList();//买方序列

ArrayListsellList = new ArrayList();//卖方序列

ArrayListvol = new ArrayList();//成交量

ArrayListShare = new ArrayList();//股票名字

ArrayListid = new ArrayList();

ArrayListShareid = new ArrayList();for(int i = 2 ;i < result.size() ;i++){for(int j = 0;j

if(j==0){

String temp=(String) result.get(i).get(j);

id.add(temp);

}if(j==3){

String temp=(String) result.get(i).get(j);

Shareid.add(temp);

}if(j==5){//price.add((String) result.get(i).get(j));

String temp =(String) result.get(i).get(j);

String[] units= temp.split("￥");

price.add(Double.valueOf(units[1]));

}if(j==7){

String temp=(String) result.get(i).get(j);

time.add(temp);//time.add((String) result.get(i).get(j));

}if(j==1){

buyList.add((String) result.get(i).get(j));

}if(j==2){

sellList.add((String) result.get(i).get(j));

}if(j==6){

vol.add(Double.valueOf((String)result.get(i).get(j)));

}if(j==4){

Share.add((String)result.get(i).get(j));

}

}

}

ArrayListresultList = new ArrayList();

resultList.add(Shareid);

resultList.add(id);

resultList.add(buyList);

resultList.add(sellList);

resultList.add(Share);

resultList.add(price);

resultList.add(vol);

resultList.add(time);returnresultList;

}

}