业务场景
客户提供一个excel文件,里面有包含大量的图片资源路径信息,需要将这些图片资源下载下来,那最好的办法就是通过java程序生成window的shell命令,然后执行命令即可实现文件下载,无需人工干预,静静的等待下载完成即可。
/** * 读取大数据量的excel数据,保存为shell命令文件 *Project:com.design.utils *Comments:<对此类的描述,可以引用系统设计中的描述> *JDKversionused:<JDK1.8> *Author:何湘简 *CreateDate:2021年5月8日 下午4:43:19 */ public class XLSXCovertCSVReader { /** * The type of the data value is indicated by an attribute on the cell. The * value is usually in a "v" element within the cell. */ enum xssfDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, } /** * 使用xssf_sax_API处理Excel,请参考: http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api * <p/> * Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at * http://www.ecma-international.org/publications/standards/Ecma-376.htm * <p/> * A web-friendly version is http://openiso.org/Ecma/376/Part4 */ class MyXSSFSheetHandler extends DefaultHandler { /** * Table with styles */ private StylesTable stylesTable; /** * Table with unique strings */ private ReadOnlySharedStringsTable sharedStringsTable; /** * Destination for data */ private final PrintStream output; /** * Number of columns to read starting with leftmost */ private final int minColumnCount; // Set when V start element is seen private boolean vIsOpen; // Set when cell start element is seen; // used when cell close element is seen. private xssfDataType nextDataType; // Used to format numeric cell values. private short formatIndex; private String formatString; private final DataFormatter formatter; private int thisColumn = -1; // The last column printed to the output stream private int lastColumnNumber = -1; // Gathers characters as they are seen. private StringBuffer value; private String[] record; private List<String[]> rows = new ArrayList<String[]>(); private boolean isCellNull = false; /** * Accepts objects needed while parsing. * * @param styles * Table of styles * @param strings * Table of shared strings * @param cols * Minimum number of columns to show * @param target * Sink for output */ public MyXSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) { this.stylesTable = styles; this.sharedStringsTable = strings; this.minColumnCount = cols; this.output = target; this.value = new StringBuffer(); this.nextDataType = xssfDataType.NUMBER; this.formatter = new DataFormatter(); record = new String[this.minColumnCount]; rows.clear();// 每次读取都清空行集合 } /* * (non-Javadoc) * * @see * org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, * java.lang.String, java.lang.String, org.xml.sax.Attributes) */ public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { if ("inlineStr".equals(name) || "v".equals(name)) { vIsOpen = true; // Clear contents cache value.setLength(0); } // c => cell else if ("c".equals(name)) { // Get the cell reference String r = attributes.getValue("r"); int firstDigit = -1; for (int c = 0; c < r.length(); ++c) { if (Character.isDigit(r.charAt(c))) { firstDigit = c; break; } } thisColumn = nameToColumn(r.substring(0, firstDigit)); // Set up defaults. this.nextDataType = xssfDataType.NUMBER; this.formatIndex = -1; this.formatString = null; String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); if ("b".equals(cellType)) nextDataType = xssfDataType.BOOL; else if ("e".equals(cellType)) nextDataType = xssfDataType.ERROR; else if ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINESTR; else if ("s".equals(cellType)) nextDataType = xssfDataType.SSTINDEX; else if ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA; else if (cellStyleStr != null) { // It's a number, but almost certainly one // with a special style or format int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); this.formatIndex = style.getDataFormat(); this.formatString = style.getDataFormatString(); if (this.formatString == null) this.formatString = BuiltinFormats .getBuiltinFormat(this.formatIndex); } } } /* * (non-Javadoc) * * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, * java.lang.String, java.lang.String) */ public void endElement(String uri, String localName, String name) throws SAXException { String thisStr = null; // v => contents of a cell if ("v".equals(name)) { // Process the value contents as required. // Do now, as characters() may be called more than once switch (nextDataType) { case BOOL: char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break; case ERROR: thisStr = "\"ERROR:" + value.toString() + '"'; break; case FORMULA: // A formula could result in a string value, // so always add double-quote characters. //thisStr = '"' + value.toString() + '"'; thisStr =value.toString(); break; case INLINESTR: // TODO: have seen an example of this, so it's untested. XSSFRichTextString rtsi = new XSSFRichTextString( value.toString()); // thisStr = '"' + rtsi.toString() + '"'; thisStr = rtsi.toString() ; break; case SSTINDEX: String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString( sharedStringsTable.getEntryAt(idx)); //thisStr = '"' + rtss.toString() + '"'; thisStr =rtss.toString(); } catch (NumberFormatException ex) { output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); } break; case NUMBER: String n = value.toString(); // 判断是否是日期格式 if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) { Double d = Double.parseDouble(n); Date date=HSSFDateUtil.getJavaDate(d); thisStr=formateDateToString(date); } else if (this.formatString != null) thisStr = formatter.formatRawCellContents( Double.parseDouble(n), this.formatIndex, this.formatString); else thisStr = n; break; default: thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; break; } // Output after we've seen the string contents // Emit commas for any fields that were missing on this row if (lastColumnNumber == -1) { lastColumnNumber = 0; } //判断单元格的值是否为空 if (thisStr == null || "".equals(isCellNull)) { isCellNull = true;// 设置单元格是否为空值 } record[thisColumn] = thisStr; // Update column if (thisColumn > -1) lastColumnNumber = thisColumn; } else if ("row".equals(name)) { // Print out any missing commas if needed if (minColumns > 0) { // Columns are 0 based if (lastColumnNumber == -1) { lastColumnNumber = 0; } // if (isCellNull == false && record[0] != null // && record[1] != null)// 判断是否空行 if (isCellNull == false )// 判断是否空行 { rows.add(record.clone()); isCellNull = false; for (int i = 0; i < record.length; i++) { record[i] = null; } } } lastColumnNumber = -1; } } public List<String[]> getRows() { return rows; } public void setRows(List<String[]> rows) { this.rows = rows; } /** * Captures characters only if a suitable element is open. Originally * was just "v"; extended for inlineStr also. */ public void characters(char[] ch, int start, int length) throws SAXException { if (vIsOpen) value.append(ch, start, length); } /** * Converts an Excel column name like "C" to a zero-based index. * * @param name * @return Index corresponding to the specified name */ private int nameToColumn(String name) { int column = -1; for (int i = 0; i < name.length(); ++i) { int c = name.charAt(i); column = (column + 1) * 26 + c - 'A'; } return column; } private String formateDateToString(Date date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期 return sdf.format(date); } } private OPCPackage xlsxPackage; private int minColumns; private PrintStream output; private String sheetName; /** * Creates a new XLSX -> CSV converter * * @param pkg * The XLSX package to process * @param output * The PrintStream to output the CSV to * @param minColumns * The minimum number of columns to output, or -1 for no minimum */ public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output, String sheetName, int minColumns) { this.xlsxPackage = pkg; this.output = output; this.minColumns = minColumns; this.sheetName = sheetName; } /** * Parses and shows the content of one sheet using the specified styles and * shared-strings tables. * * @param styles * @param strings * @param sheetInputStream */ public List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException { InputSource sheetSource = new InputSource(sheetInputStream); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns, this.output); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); return handler.getRows(); } /** * 初始化这个处理程序 将 * * @throws IOException * @throws OpenXML4JException * @throws ParserConfigurationException * @throws SAXException */ public List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable( this.xlsxPackage); XSSFReader xssfReader = new XSSFReader(this.xlsxPackage); List<String[]> list = null; StylesTable styles = xssfReader.getStylesTable(); XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader .getSheetsData(); int index = 0; while (iter.hasNext()) { InputStream stream = iter.next(); String sheetNameTemp = iter.getSheetName(); if (this.sheetName.equals(sheetNameTemp)) { list = processSheet(styles, strings, stream); stream.close(); ++index; } } return list; } /** * 读取Excel * * @param path * 文件路径 * @param sheetName * sheet名称 * @param minColumns * 列总数 * @return * @throws SAXException * @throws ParserConfigurationException * @throws OpenXML4JException * @throws IOException */ private static List<String[]> readerExcel(String path, String sheetName, int minColumns) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { OPCPackage p = OPCPackage.open(path, PackageAccess.READ); XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out, sheetName, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; } public static void main(String[] args) throws Exception { System.out.println("开始导入数据..."); List<String[]> list = XLSXCovertCSVReader .readerExcel( "F:/工作文档/智慧环卫/377672(1).xlsx", "Sheet1", 10); Map map1=new HashMap(); Map map2=new HashMap(); File tFile = new File("C:\\Users\\Administrator\\Desktop\\spany-down-ps.txt");//.ps1 if (!tFile.exists()) { try { tFile.createNewFile(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } FileWriter fileWritter = new FileWriter("C:\\Users\\Administrator\\Desktop\\命令\\spany-down-ps0.ps1",true); fileWritter.write("$client = new-object System.Net.WebClient \n"); FileWriter pathWritter = new FileWriter("C:\\Users\\Administrator\\Desktop\\命令\\spany-down-path-ps.ps1",true); pathWritter.write("$client = new-object System.Net.WebClient \n"); int qqq=0,ss=0; Map<String,String> fileMap= new HashMap<String,String>(); for(int i=1;i<list.size();i++){ String[] record=list.get(i); String url="http://192.162.0.201:9000/"; String savePath="e:/抓取图片资源/"; //遍历所有的列 savePath+=record[2]+"/"; savePath+=record[3]+"/"; savePath+=record[4]+"/"; JSONArray ja = JSONArray.fromObject(record[0]); for (int s=0;s<ja.size();s++) { JSONObject jo=ja.getJSONObject(s); if(jo.containsKey("filePath")) { String filePath=jo.getString("filePath"); System.out.println("filePath=="+filePath); if(filePath.startsWith("app/")) { qqq++; if(qqq>50000) { fileWritter.close(); ss++; fileWritter = new FileWriter("C:\\Users\\Administrator\\Desktop\\命令\\spany-down-ps"+ss+".ps1",true); fileWritter.write("$client = new-object System.Net.WebClient \n"); qqq=0; } if(!fileMap.containsKey(savePath)) { pathWritter.write("mkdir "+savePath+"\n"); } fileMap.put(savePath, savePath); fileWritter.write("$client.DownloadFile('"+url+filePath+"','"+savePath+jo.getString("fileName")+"')\n"); } } } } fileWritter.close(); pathWritter.close(); System.out.println("数据书目数据为"+list.size()+"条"); System.out.println("查出书目数据为"+map1.size()+"条"); System.out.println("已有书目数据为"+map2.size()+"条"); System.out.println("导入数据成功,开始分析数据..."); } static String newExcelPath=("d:\\book2.xlsx"); public static boolean createExcelFile(List list ,Map map1,Map map2) { boolean isCreateSuccess = false; SXSSFWorkbook workbook = null; try { // XSSFWork used for .xslx (>= 2007), HSSWorkbook for 03 .xsl workbook = new SXSSFWorkbook(500);//HSSFWorkbook();//WorkbookFactory.create(inputStream); }catch(Exception e) { System.out.println("It cause Error on CREATING excel workbook: "); e.printStackTrace(); } if(workbook != null) { CellStyle style =workbook.createCellStyle() ; style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Sheet sheet = workbook.createSheet("testdata"); System.out.println(sheet.getColumnWidth(0)); for (int rowNum = 0; rowNum < list.size(); rowNum++) { Row row = sheet.createRow(rowNum); String[] record=(String[])list.get(rowNum); for(int i=0;i<record.length;i++){ sheet.setColumnWidth(i, 3000); sheet.setColumnWidth(2, 12000); Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellValue(record[i]); if(record[9]!=null&&record[9].equals("已有")){ if(map2.get(record[2])!=null){ row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue("重复"); } }else{ if(map1.get(record[2])!=null){ row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue("重复"); } cell.setCellStyle(style); } } } System.out.println("分析数据完成,开始写入文件..."); try { FileOutputStream outputStream = new FileOutputStream(newExcelPath); workbook.write(outputStream); outputStream.flush(); outputStream.close(); isCreateSuccess = true; } catch (Exception e) { System.out.println("It cause Error on WRITTING excel workbook: "); e.printStackTrace(); } } File sss = new File(newExcelPath); System.out.println(sss.getAbsolutePath()); return isCreateSuccess; } }
依赖以上jar,文件生成以后直接使用Windows PowerShell执行脚本即可
通过windows powershell 工具批量下载
首先查询出数据库中唯一的文件路径,通过以下sql语句查询
SELECT concat('mkdir ',LEFT(realpath, LENGTH(realpath) - LOCATE('/', REVERSE(realpath)))) FROM t_s_attachment GROUP BY concat('mkdir ',LEFT(realpath, LENGTH(realpath) - LOCATE('/', REVERSE(realpath))));
新建spany-down-path.ps1文件,将查询结果保存到spany-down-path.ps1文件中
新建spany-down-urls.ps1文件,将以下内容复制粘贴保存
$client = new-object System.Net.WebClient
执行以下sql,将结果保存在spany-down-urls.ps1文件后面
SELECT concat('$client.DownloadFile(\'http://47.109.80.36/attach/',realpath,'\'',',\'F:/Download/',realpath,'\')') FROM t_s_attachment GROUP BY realpath;
最后
通过window powershell cd到目标文件夹,先执行spany-down-path.ps1文件命令创建文件夹,再执行spany-down-urls.ps1下载文件,静静的等待文件下载完成即可
本文由 admin 创作,采用 知识共享署名4.0
国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为:2022-08-09 22:38:29