记录：Poi处理Excel并保存到TxT

场景：
处理公司一个宕机的request_msg.日志

处理思路：

日志是存在excel中

本来想excel中的单元格读取过来转为xml对象，然后取节点里的值，一直没成功，就使用了截取的方式。

使用Poi来读取Excel，并把request中需要的xml节点值截取出来，拼接输出到一个txt中

代码:

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;import java.io.*;/** * Created by sun.song on 2016/12/7. */public class TestDai {    public static void main(String[] args) throws Exception {        FileWriter writer=null;        int a =0;        //头处理        //String sr[]={"<ns2:uniqueKey>","<ns3:tmId>","<ns3:status>","<ns3:volume>","<ns3:weight>","<ns3:orderId>"};        //String sr2[]={"</ns2:uniqueKey>","</ns3:tmId>","</ns3:status>","</ns3:volume>","</ns3:weight>","</ns3:orderId>"};        //行处理        String sr[]={"<ns3:orderId>","<ns3:orderType>","<ns3:skuId>","<ns3:lineId>","<ns3:linePk>","<ns3:status>","<ns3:quantity>","<ns3:hostState>","<ns3:held>","<ns3:reason>"};        String sr2[]={"</ns3:orderId>","</ns3:orderType>","</ns3:skuId>","</ns3:lineId>","</ns3:linePk>","</ns3:status>","</ns3:quantity>","</ns3:hostState>","</ns3:held>","</ns3:reason>"};        File file = new File("C:\\Users\\sun.song\\Desktop\\ads.xlsx");        FileInputStream inputStream = new FileInputStream(file);        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);        // 循环工作表Sheet        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);            if (xssfSheet == null) {                continue;            }            // 循环行Row            for (int rowNum = 0; rowNum < xssfSheet.getPhysicalNumberOfRows(); rowNum++) {                XSSFRow xssfRow = xssfSheet.getRow(rowNum);                System.out.println("第"+rowNum+"行");                if (xssfRow == null) {                    continue;                }                //循环cell                for (int cellNum = 0; cellNum <xssfRow.getPhysicalNumberOfCells() ; cellNum++) {                    XSSFCell cell = xssfRow.getCell(cellNum);                    if (cell==null){                        continue;                    }                    String value = getValue(cell);                    String handleString = handleString(value, sr, sr2);                    String s = handleString + "\n";                    writer = new FileWriter("d:/3.txt", true);                    writer.write(s);                    a++;                    System.out.println("添加了"+a+"条");                    if (writer!=null){                        writer.close();                    }                }            }        }    }    /**     *     * @param v xml格式的String     * @param first 起始节点数组     * @param last  结束节点数组     * @return 返回处理过的字符串     */    public static String handleString(String v, String[] first, String[] last) {        StringBuffer sb= new StringBuffer("");        for (int i = 0; i < first.length; i++) {            String firstString = first[i];            String lastString = last[i];                //System.out.println(firstString);                int firstStringLength = firstString.length();                StringBuffer bufferCellValue = new StringBuffer(v);                // System.out.println(cellValue);                    int firstStringIndex = v.indexOf(firstString);                    //拿出在字符串里不是空的，并截取                if (firstStringIndex>0){                    int lastStringIndex = v.indexOf(lastString);                    StringBuffer subs = new StringBuffer(";"+bufferCellValue.substring(firstStringLength + firstStringIndex, lastStringIndex));                    sb.append(subs);                }else {                    StringBuffer sbs = new StringBuffer(";");                    sb.append(sbs);                }        }        return sb.toString();    }    //根据单元个类型获取单元格    private static String getValue(XSSFCell xssfCell) {        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {            // 返回布尔类型的值            return String.valueOf(xssfCell.getBooleanCellValue());        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {            // 返回数值类型的值            return String.valueOf(xssfCell.getNumericCellValue());        } else {            // 返回字符串类型的值            return String.valueOf(xssfCell.getStringCellValue());        }    }   /* //格式化XML字符串    public static String formatXml(String str) throws Exception {        Document document = null;        document = DocumentHelper.parseText(str);        // 格式化输出格式        OutputFormat format = OutputFormat.createPrettyPrint();        format.setEncoding("gb2312");        StringWriter writer = new StringWriter();        // 格式化输出流        XMLWriter xmlWriter = new XMLWriter(writer, format);        // 将document写入到输出流        xmlWriter.write(document);        xmlWriter.close();        return writer.toString();    }    public static Document xml2Doc2(String xmlStr){        SAXBuilder builder = new SAXBuilder();        StringReader sr = new StringReader(xmlStr);        InputSource is = new InputSource(sr);        Document document = null;        try {            document=builder.build(is);        } catch (JDOMException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        return document;    }    */}