Java寫入寫出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相關專題，如有不足之處，歡迎留言指出。感謝朋友們對本站的支持！