欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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写入写出excel操作源码分享的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站java相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!

上一篇:

下一篇: