import java.io.File; import java.io.IOException; import java.io.InputStreamReader; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.apache.commons.io.FileUtils; import au.com.bytecode.opencsv.CSVReader; public class ReadCsv { @SuppressWarnings("unchecked") public static List<String[]> readCsv(String excelFilePath, int startRowNum){ List<String[]> excelContent = new ArrayList<String[]>(); CSVReader reader = null; File tempFile = null; try { tempFile = new File(excelFilePath); reader = new CSVReader(new InputStreamReader(FileUtils.openInputStream(tempFile))); excelContent = reader.readAll(); if(startRowNum>0){ Iterator<String[]> it = excelContent.iterator(); int i=0; while(it.hasNext()){ it.next(); if(i<startRowNum){ it.remove(); i++; }else{ break; } } } reader.close(); } catch (IOException e) { e.printStackTrace(); } return excelContent; } }
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; 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.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import au.com.bytecode.opencsv.CSVWriter; /** * 读取Excel中的文件 * * @author JS * @date 2013-06-18 */ public class ReadExcel { public ReadExcel() { } /** * 读取Excel文件中的数据(支持2003和2007) * * @param excelFilePath Excel文件存放的路径 * @param startRowNum 解析Excel文件,从哪一行开始0表示从第一行开始 * * @return * * @throws IOException * * @author JS */ public static List<String[]> read2003And2007(String excelFilePath, int startRowNum) throws IOException{ List<String[]> excelContent = new ArrayList<String[]>(); Cell cell = null; Row row=null; Sheet sheet=null; Workbook wb = null; InputStream stream=null; try { if (excelFilePath.endsWith(".xls")){ stream = new FileInputStream(excelFilePath); wb = (Workbook) new HSSFWorkbook(stream); } else if (excelFilePath.endsWith(".xlsx")){ wb = (Workbook) new XSSFWorkbook(excelFilePath); } if(wb==null){ return null; } DecimalFormat df = new DecimalFormat("####.##"); SimpleDateFormat smtDate = new SimpleDateFormat("yyyy/MM/dd"); sheet = wb.getSheetAt(0); // 行数(从0开始,相当于最后一行的索引) int count_row=sheet.getPhysicalNumberOfRows(); Row firstRow=sheet.getRow(0); if(firstRow==null){ return null; } String[] temp=null; //列数 int count_cell=firstRow.getPhysicalNumberOfCells(); for (int i = startRowNum; i <=count_row; i++) { row=sheet.getRow(i); if(row==null){ continue; } temp=new String[count_cell]; for (int j = 0; j < count_cell; j++) { cell=row.getCell(j); if(null == cell) { temp[j] = ""; continue; } int type = cell.getCellType(); // 得到单元格数据类型 switch (type) { // 判断数据类型 case Cell.CELL_TYPE_BLANK: temp[j] = ""; break; case Cell.CELL_TYPE_BOOLEAN: temp[j] = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_ERROR: temp[j] = cell.getErrorCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: temp[j] = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = HSSFDateUtil.getJavaDate(d); temp[j] = smtDate.format(date); }else{ temp[j] = df.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: temp[j] = cell.getStringCellValue(); break; default: temp[j] = cell.getStringCellValue(); break; } if (null != temp[j])temp[j] = temp[j].trim(); } excelContent.add(temp); } cell=null; row=null; sheet=null; wb=null; temp=null; } catch (Exception e) { e.printStackTrace(); }finally{ if(stream!=null){ stream.close(); } } return excelContent; } public static List<String[]> readSheet(Sheet sheet,int startRowNum) throws IOException{ if(sheet==null){ return null; } List<String[]> excelContent = new ArrayList<String[]>(); Cell cell = null; Row row=null; DecimalFormat df = new DecimalFormat("####.##"); // 行数(从0开始,相当于最后一行的索引) int count_row=sheet.getPhysicalNumberOfRows(); //列数 Row firstRow=sheet.getRow(0); if(firstRow==null){ return null; } int count_cell=firstRow.getPhysicalNumberOfCells(); for (int i = startRowNum; i <=count_row; i++) { row=sheet.getRow(i); if(row==null){ continue; } String temp[] = new String[count_cell]; for (int j = 0; j < count_cell; j++) { cell=row.getCell(j); if(null == cell) { temp[j] = ""; continue; } int type = cell.getCellType(); // 得到单元格数据类型 switch (type) { // 判断数据类型 case Cell.CELL_TYPE_BLANK: temp[j] = ""; break; case Cell.CELL_TYPE_BOOLEAN: temp[j] = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_ERROR: temp[j] = cell.getErrorCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: temp[j] = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: temp[j] = df.format(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: temp[j] = cell.getStringCellValue(); break; default: temp[j] = cell.getStringCellValue(); break; } if (null != temp[j])temp[j] = temp[j].trim(); } excelContent.add(temp); } cell=null; row=null; sheet=null; return excelContent; } @SuppressWarnings("unchecked") public static void exportExcel(List<Object> listRow,String[] titles,String fileName){ OutputStream out=null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow rowtitle = sheet.createRow(0); for (int i = 0; i < titles.length; i++) { HSSFCell cell = rowtitle.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titles[i]); } for (int i = 1; i <= listRow.size(); i++) { HSSFRow row = sheet.createRow(i); List<String> values=((List<String>)listRow.get(i-1)); if(values!=null&&values.size()>0){ for (int j = 0; j < values.size(); j++) { HSSFCell cell = row.createCell( j); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(values.get(j)); } } } File file=new File(fileName); out = new FileOutputStream(file); wb.write(out); out.flush(); } catch (Exception e) { e.printStackTrace(); }finally{ try { if(out!=null){ out.close(); } } catch (Exception e2) { } } } }
相关推荐
java 导入Excel 文件,兼容Excel 2003(后缀名:xls)及 2007(后缀名:xlsx)的文件,同时还支持csv格式的文件
支持从数据库中查询到数据,以多级表头形式导入到Excel文件中。比如:一级表头:学号、姓名、各科成绩。二级表头:语文、数学、英语(二级表头在各科成绩下面)。
java导出CVS,csv只是一种文件的形式。。。就是按照逗号(英文逗号)分隔的文件 它的做成跟普通的文件做成是一样一样的,只需要创建一个文件(*.csv),把字符串按照一定的格式(AAA,BBB\nCCC,DDD注意必须是英文的...
使用POI技术对Excel导入导出及支持CSV文件导入导出。
小demo,直接运行Test.java就行了 导出文件在D盘
轻松解决普通poi形式导出Excel的中出现的栈溢出问题,此资源可实现千万级数据分批导出csv文件,测试实现16500000条数据大概80秒左右;具体表里内容。
easypoi常用导入,导出,工具,引入easypoi包ExcelUtil工具类,可以直接导出查询的数据
poi读取大量数据会造成gc内存溢出的报错,由于垃圾回收机制无法将大量的对象及时的回收,而这些对象又会保存在内存中,会导致内存不够用的情况,这时候我们就需要使用新的方法,读取为cvs即可.此解决方案可支持千万数据的...
InfluxDB查询 导出数据 mybatis SpringBoot
帮助解决网页和JS文件中的中文编码问题的小工具 慎用const关键字 装箱,拆箱以及反射 动态调用对象的属性和方法——性能和灵活性兼备的方法 消除由try/catch语句带来的warning 微软的应试题完整版(附答案) 一个...