我要评论这两天帮老师做一个数据库，将所有实验交易的数据导入到数据库中，但是不想天天在实验室里面待着，气氛太压抑，就想着先把数据读进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相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论