These two days, I helped the teacher make a database and import all the data of the experimental transactions into the database. But I didn’t want to stay in the laboratory every day. The atmosphere was too depressing, so I thought about reading the data into EXCEL first and bringing it to the laboratory to import it.
The data turned out to be like this. Different experiments have a special folder. There are folders for different classes under the folder of the experiment name. The class date folder is stored in the EXCEL data for the experiments in different times. The original EXCEL did not have class and time. Now you need to add this information as a column by reading the EXCEL name and class name.
Below is the source code, hehe, and a visualization window was also made.
Class 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;}//Format private static DecimalFormat df = new DecimalFormat("0");// Default cell formatting date string private static SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");// Format number 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")){//process ecxel2007 return readExcel2007(file);} else{//process ecxel2003 return readExcel2003(file);}}/* * @return Store the return result in ArrayList, and the storage structure is similar to a two-digit array* lists.get(0).get(0) means 0 rows and 0 columns in Excel in the past */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){//When the read behavior is empty if(i != sheet.getPhysicalNumberOfRows()){//Defend whether it is the last line 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){//When the cell is empty if(j != row.getLastCellNum()){//Defend whether it is the last cell in the row colList.add("");}continue;}switch(cell.getCellType()){case XSSFCell.CELL_TYPE_STRING: //System.out.println(i + "row" + j + "column 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 + "row" + j // + " column is Number type ; DateFormt:" // + value.toString()); break;case XSSFCell.CELL_TYPE_BOOLEAN: //System.out.println(i + "row" + j + " Column is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break;case XSSFCell.CELL_TYPE_BLANK: //System.out.println(i + "row" + j + " Column is Blank type"); value = "";break;default: //System.out.println(i + "row" + j + " Column is default type"); value = cell.toString();}// end switch colList.add(value);}// end switch 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){//When the read behavior is empty if(i != sheet.getPhysicalNumberOfRows()){//Judge whether it is the last row 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){//When the cell is empty if(j != row.getLastCellNum()){//Judge whether it is the last cell in the row colList.add("");}continue;}switch(cell.getCellType()){case XSSFCell.CELL_TYPE_STRING: //System.out.println(i + "row" + j + "column 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 + "row" + j // + " Column is Number type ; DateFormt:" // + value.toString()); break;case XSSFCell.CELL_TYPE_BOOLEAN: //System.out.println(i + "row" + j + "column is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break;case XSSFCell.CELL_TYPE_BLANK: //System.out.println(i + "row" + j + "column is Blank type"); value = "";break;default: //System.out.println(i + "row" + j + "column is default type"); value = cell.toString();}// end switch colList.add(value);}// end for j rowList.add(colList);}// end for i return rowList;}// 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("Transaction")){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);//The location of storage after Excel file is generated. 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>();//Price sequence ArrayList<String>time = new ArrayList<String>();//Time series ArrayList<String>buyList = new ArrayList<String>();//Buyer sequence ArrayList<String>sellList = new ArrayList<String>();//Seller sequence ArrayList<double>vol = new ArrayList<double>();//Trading volume ArrayList<String>Share = new ArrayList<String>();//Stock name 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++){//Column 5 represents the price, and column 8 represents the time 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 (for visualization window):
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(), "Select");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);}}Running results:
Summarize
The above is all the content of this article about Java writing and writing Excel operation source code sharing, I hope it will be helpful to everyone. Interested friends can continue to refer to the Java-related topics on this website. If there are any shortcomings, please leave a message to point it out. Thank you friends for your support for this site!