Java寫入寫出Excel操作源碼分享

2024-07-13 10:14:46

字體：大中小

來源：轉載

供稿：網友

這兩天幫老師做一個數據庫，將所有實驗交易的數據導入到數據庫中，但是不想天天在實驗室里面待著，氣氛太壓抑，就想著先把數據讀進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,源碼