首页 > 代码库 > java写入和写出EXCEL（含源代码）
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 =http://www.mamicode.com/ 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 =http://www.mamicode.com/ Boolean.valueOf(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: //System.out.println(i + "行" + j + " 列 is Blank type"); valuehttp://www.mamicode.com/= ""; break; default: //System.out.println(i + "行" + j + " 列 is default type"); value =http://www.mamicode.com/ 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 =http://www.mamicode.com/ 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 =http://www.mamicode.com/ Boolean.valueOf(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: //System.out.println(i + "行" + j + " 列 is Blank type"); valuehttp://www.mamicode.com/= ""; break; default: //System.out.println(i + "行" + j + " 列 is default type"); value =http://www.mamicode.com/ 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（含源代码）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。