Java写入写出Excel操作源码分享
程序员文章站
2023-12-12 13:50:40
这两天帮老师做一个数据库,将所有实验交易的数据导入到数据库中,但是不想天天在实验室里面待着,气氛太压抑,就想着先把数据读进excel中,哪天带到实验室导进去
数据原来是这...
这两天帮老师做一个数据库,将所有实验交易的数据导入到数据库中,但是不想天天在实验室里面待着,气氛太压抑,就想着先把数据读进excel中,哪天带到实验室导进去
数据原来是这样的,不同的实验有一个专门的文件夹,实验名的文件夹下有不同班级的文件夹,班级文件夹下有该班级日期文件夹,存储的是不同时间下该班做实验的数据excel,原来的excel中没有班级和时间,现在需要通过读取excel名以及班级名来将该信息作为一列,加入到excel中。
下面是源代码,嘿嘿,顺便还做了一个可视化窗口。
类excelread:
import java.awt.list; import java.io.bytearrayoutputstream; import java.io.file; import java.io.fileinputstream; import java.io.fileoutputstream; import java.io.ioexception; import java.io.outputstream; import java.text.decimalformat; import java.text.simpledateformat; import java.util.arraylist; 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.xssf.usermodel.xssfcell; import org.apache.poi.xssf.usermodel.xssfrow; import org.apache.poi.xssf.usermodel.xssfsheet; import org.apache.poi.xssf.usermodel.xssfworkbook; public class excelread { string path; public string getpath() { return path; } public void setpath(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<arraylist<object>> readexcel(file file){ if(file == null){ return null; } if(file.getname().endswith("xlsx")){ //处理ecxel2007 return readexcel2007(file); } else{ //处理ecxel2003 return readexcel2003(file); } } /* * @return 将返回结果存储在arraylist内,存储结构与二位数组类似 * lists.get(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 = 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<object>(); 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()){ case xssfcell.cell_type_string: //system.out.println(i + "行" + j + " 列 is string type"); value = cell.getstringcellvalue(); break; case xssfcell.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; case xssfcell.cell_type_boolean: //system.out.println(i + "行" + j + " 列 is boolean type"); value = boolean.valueof(cell.getbooleancellvalue()); 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 = cell.tostring(); } // end switch collist.add(value); } //end for j rowlist.add(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 = 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<object>(); 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()){ case xssfcell.cell_type_string: //system.out.println(i + "行" + j + " 列 is string type"); value = cell.getstringcellvalue(); break; case xssfcell.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; case xssfcell.cell_type_boolean: //system.out.println(i + "行" + j + " 列 is boolean type"); value = boolean.valueof(cell.getbooleancellvalue()); 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 = cell.tostring(); } // end switch collist.add(value); } //end for j rowlist.add(collist); } //end for i return rowlist; } catch(exception e){ system.out.println("exception"); return null; } } public static arraylist getfiles(string filepath){ file root = new file(filepath); file[]files = root.listfiles(); arraylist filelist = new arraylist(); for (file file:files){ if(file.isdirectory()){ filelist.addall(getfiles(file.getabsolutepath())); } else{ string newpath = file.getabsolutepath(); if(newpath.contains("交易记录")){ filelist.add(newpath); } } } return filelist; } public void readbook(string path3) { string filepath = path3; arraylist filelist = getfiles(filepath); arraylist<arraylist>resultall = new arraylist<arraylist>(); for (int i = 0;i<filelist.size();i++){ string path = (string) filelist.get(i); system.out.println(path); arraylist<arraylist>result = graph(path); string[] path2 = path.split("\\\\"); int num = result.get(0).size(); arraylist result2 = new arraylist(); for (int j = 0;j<num;j++){ result2.add(path2[path2.length-2]); } arraylist result3 = new arraylist(); for (int j = 0;j<num;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<result.size();j++){ for (int k = 0;k<result.get(j).size();k++){ resultall.get(j).add(result.get(j).get(k)); } } } } writeexcel(resultall,"d:/a.xls"); } public static void writeexcel(arraylist<arraylist> result,string path){ if(result == null){ return; } hssfworkbook wb = new hssfworkbook(); 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 = new bytearrayoutputstream(); try { wb.write(os); } catch (ioexception e){ e.printstacktrace(); } byte[] content = os.tobytearray(); file file = new file(path); //excel文件生成后存储的位置。 outputstream fos = null; try { fos = new fileoutputstream(file); wb.write(fos); os.close(); fos.close(); } catch (exception e){ e.printstacktrace(); } } public static decimalformat getdf() { return df; } public static void setdf(decimalformat df) { excelread.df = df; } public static simpledateformat getsdf() { return sdf; } public static void setsdf(simpledateformat sdf) { excelread.sdf = sdf; } public static decimalformat getnf() { return nf; } public static void setnf(decimalformat nf) { excelread.nf = nf; } public static arraylist<arraylist> graph(string path){ file file = new file(path); arraylist<arraylist<object>> result = excelread.readexcel(file); arraylist<double>price = new arraylist<double>(); //价格序列 arraylist<string>time = new arraylist<string>(); //时间序列 arraylist<string>buylist = new arraylist<string>(); //买方序列 arraylist<string>selllist = new arraylist<string>(); //卖方序列 arraylist<double>vol = new arraylist<double>(); //成交量 arraylist<string>share = new arraylist<string>(); //股票名字 arraylist<string>id = new arraylist<string>(); arraylist<string>shareid = new arraylist<string>(); for (int i = 2 ;i < result.size() ;i++){ for (int j = 0;j<result.get(i).size(); j++){ //第5列表示价格,第8列表示时间 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)); } } } arraylist<arraylist>resultlist = new arraylist<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); return resultlist; } }
readexcelbook(做可视化窗口的):
import java.awt.eventqueue; import javax.swing.jfilechooser; import javax.swing.jframe; import javax.swing.grouplayout; import javax.swing.jlabel; import javax.swing.grouplayout.alignment; import javax.swing.jbutton; import javax.swing.jtextfield; import java.awt.event.actionlistener; import java.awt.event.actionevent; import java.io.file; public class readexcelbook { private jframe frame; private jtextfield textfield; /** * launch the application. */ public static void main(string[] args) { eventqueue.invokelater(new runnable() { public void run() { try { readexcelbook window = new readexcelbook(); window.frame.setvisible(true); } catch (exception e) { e.printstacktrace(); } } } ); } /** * create the application. */ public readexcelbook() { initialize(); } /** * initialize the contents of the frame. */ private void initialize() { frame = new jframe(); frame.setbounds(100, 100, 450, 300); frame.setdefaultcloseoperation(jframe.exit_on_close); jbutton button = new jbutton("\u9009\u62e9\u6587\u4ef6"); button.addactionlistener(new actionlistener() { public void actionperformed(actionevent e) { jfilechooser jfc=new jfilechooser(); jfc.setfileselectionmode(jfilechooser.files_and_directories ); jfc.showdialog(new jlabel(), "选择"); file file=jfc.getselectedfile(); string path = file.getabsolutepath(); textfield.settext(path); excelread er = new excelread(); er.readbook(path); } } ); textfield = new jtextfield(); textfield.setcolumns(10); jlabel lbldaxls = new jlabel("\u5199\u5165\u4e86d\u76d8\u4e0b\u7684a.xls\u54c8"); grouplayout grouplayout = new grouplayout(frame.getcontentpane()); grouplayout.sethorizontalgroup( grouplayout.createparallelgroup(alignment.leading) .addgroup(grouplayout.createsequentialgroup() .addgap(26) .addgroup(grouplayout.createparallelgroup(alignment.leading) .addcomponent(lbldaxls) .addcomponent(textfield, grouplayout.preferred_size, 295, grouplayout.preferred_size) .addcomponent(button)) .addcontainergap(113, short.max_value)) ); grouplayout.setverticalgroup( grouplayout.createparallelgroup(alignment.leading) .addgroup(grouplayout.createsequentialgroup() .addgap(31) .addcomponent(button) .addgap(18) .addcomponent(textfield, grouplayout.preferred_size, grouplayout.default_size, grouplayout.preferred_size) .addgap(35) .addcomponent(lbldaxls) .addcontainergap(119, short.max_value)) ); frame.getcontentpane().setlayout(grouplayout); } }
运行结果:
总结
以上就是本文关于java写入写出excel操作源码分享的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站java相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!