java 解析excle
jjava解析excle或者csv文件并导出到web界面:
创建ExcelShower.java
package com.ssm.controller; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.HttpURLConnection; import java.net.URL; import java.net.URLDecoder; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import net.sf.json.JSONArray; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import com.pera.taskstat.excelToHtml.CSVFileUtil; import com.pera.taskstat.excelToHtml.POIReadExcelToHtml03; /** * * @author yeyun * @Date 2016-06-015 * @Describtion 解析数值模型的excel控制层 */ @Controller @RequestMapping(value="view/show/") public class ExcelShower{ /** * SID. */ private static final long serialVersionUID = -8344971443770122206L; /** * 读取 Excel 显示页面. * @param properties * @return * @throws Exception */ @SuppressWarnings("deprecation") @RequestMapping(value="read.action") public String read(HttpServletRequest request,HttpServletResponse response) throws Exception { //定义一个字符串,用向前台传输拼写完毕的html页面 String htmlExcel=""; String m=""; InputStream inputStream=null; HttpURLConnection httpURLConnection=null; Element element = null; DocumentBuilder db = null; DocumentBuilderFactory dbf = null; //读取a.xml文件 File f = new File(getClass().getClassLoader().getResource("/config/a.xml").getPath()); List<String> listString=new ArrayList<String>(); //解析a.xml的内容,拼写读取文件的路径 try { dbf = DocumentBuilderFactory.newInstance(); db = dbf.newDocumentBuilder(); Document dt = db.parse(f); element = dt.getDocumentElement(); NodeList childNodes = element.getChildNodes(); for (int i = 0; i < childNodes.getLength(); i++) { Node node1 = childNodes.item(i); listString.add(node1.getTextContent()); } //要读取文件的路径 m=listString.get(1)+listString.get(3)+"."+listString.get(5); } catch (Exception e) { e.printStackTrace(); } //拼写出完整的url String u_url="http://"+m; System.out.println(u_url); //根据url去服务器读取文件 try{ URLDecoder decoder = new URLDecoder(); String path = decoder.decode(u_url,"UTF-8"); // URL url = new URL(null,path,new com.sun.net.ssl.internal.www.protocol.https.Handler()); URL url = new URL(path); System.out.println("url ========= "+url); if(url!=null){ httpURLConnection=(HttpURLConnection) url.openConnection(); httpURLConnection.setConnectTimeout(3000); httpURLConnection.setRequestMethod("GET"); int responseCode=httpURLConnection.getResponseCode(); if(responseCode==200){ inputStream=httpURLConnection.getInputStream(); } } }catch(Exception e){ System.out.println("错误信息:"+e.getMessage()); } //引入poi的工具类 POIReadExcelToHtml03 poire = new POIReadExcelToHtml03(); //取得url中完整的文件名称 int index = u_url.lastIndexOf("/"); char[] ch = u_url.toCharArray(); String lastString =String.copyValueOf(ch, index + 1, ch.length - index - 1); String a_lastString=lastString.substring(lastString.length()-3,lastString.length()); //判断csv还是xls格式 if(a_lastString.equals("csv")){ //顶用CSVFileUtil工具类方法 CSVFileUtil cSVFileUtil=new CSVFileUtil(); htmlExcel=cSVFileUtil.readCsvFile(inputStream, "utf-8"); }else{ HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //获整个Excel htmlExcel = poire.getExcelInfo(workbook); } //放入集合,返回页面 List<String> list=new ArrayList<String>(); list.add(htmlExcel); //转为json格式 String json=JSONArray.fromObject(list).toString(); //响应前台编码 response.setContentType("application/json;charset=UTF-8"); try { response.getWriter().print(json); } catch (IOException e) { e.printStackTrace(); } return null; } }
CSVFileUtil.java
package com.pera.taskstat.excelToHtml; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.util.ArrayList; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; import net.sf.json.JSONArray; public class CSVFileUtil { /** * 构造,禁止实例化 */ public static void main(String[] args) { // String argPath = "D:/test/435453453.csv"; String argPath = "F://day01.csv"; // readCsvFile(argPath, "gbk"); } /** * csv文件读取<BR/> * 读取绝对路径为argPath的csv文件数据,并以List返回。 * * @param argPath csv文件绝对路径 * @return csv文件数据(List<String[]>) * @throws FileNotFoundException * @throws IOException */ public String readCsvFile(InputStream reader, String encoding) { List<String[]> list = new ArrayList<String[]>(); // File file = new File(argPath); // FileInputStream input = null; // InputStreamReader reader = null; BufferedReader bReader = null; try { // input = new FileInputStream(file); // if (encoding == null) // { // reader = new InputStreamReader(input); // } // else // { // reader = new InputStreamReader(input, encoding); // } bReader = new BufferedReader(new InputStreamReader(reader)); String str = bReader.readLine(); String str1 = ""; Pattern pCells = Pattern.compile("(\"[^\"]*(\"{2})*[^\"]*\")*[^,]*,"); while ((str = bReader.readLine()) != null) { if (!str.endsWith(",")) { str = str + ","; } Matcher mCells = pCells.matcher(str); List<String> listTemp = new ArrayList<String>(); // 读取每个单元格 while (mCells.find()) { str1 = mCells.group(); str1 = str1.replaceAll( "(?sm)\"?([^\"]*(\"{2})*[^\"]*)\"?.*,", "$1"); str1 = str1.replaceAll("(?sm)(\"(\"))", "$2"); listTemp.add(str1); } list.add((String[]) listTemp.toArray(new String[listTemp.size()])); } } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (null != bReader) { try { bReader.close(); } catch (IOException e) { e.printStackTrace(); } } } StringBuffer lsb = new StringBuffer(); lsb.append("<table width=100% style=\"border:2px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;border-collapse:collapse;\">"); for(int i=0;i<list.size();i++){ lsb.append("<tr style=\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\">"); for(int j=0;j<list.get(i).length;j++){ lsb.append("<td style=\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\">"+list.get(i)[j]+"</td>"); } lsb.append("</tr>"); } lsb.append("</table>"); String a=lsb.toString(); List<String> sList=new ArrayList<String>(); sList.add(a); String sourceListJson=JSONArray.fromObject(sList).toString(); System.out.println("sourceListJson:"+sourceListJson); return a; } }
POIReadExcelToHtml03.java
package com.pera.taskstat.excelToHtml; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.text.DecimalFormat; import java.util.HashMap; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; public class POIReadExcelToHtml03 { public static void main(String[] args) { try { POIReadExcelToHtml03 poire = new POIReadExcelToHtml03(); String path = "C://SURFACE_UWYO_HOUR.xls"; File sourcefile = new File(path); InputStream is = new FileInputStream(sourcefile); //POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(is); String htmlExcel = poire.getExcelInfo(wb); System.out.println(poire.getExcelInfo(wb)); is.close(); } catch (Exception e) { e.printStackTrace(); } } public String getHtmlExcel(HSSFWorkbook wb){ try { String htmlPage = getExcelInfo(wb); return htmlPage; } catch (Exception e) { e.printStackTrace(); } return null; } public String getExcelInfo(HSSFWorkbook wb) throws Exception { StringBuffer sb = new StringBuffer(); Sheet sheet = wb.getSheetAt(0); int lastRowNum = sheet.getLastRowNum(); Map<String, String> map[] = getRowSpanColSpanMap(sheet); sb.append("<table style='border-collapse:collapse;' width='100%'>"); HSSFRow row = null; HSSFCell cell = null; // System.out.println(sheet.getPhysicalNumberOfRows()); for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { row = (HSSFRow) sheet.getRow(rowNum); if (row == null) { sb.append("<tr><td > </td></tr>"); continue; } sb.append("<tr>"); int lastColNum = row.getLastCellNum(); for (int colNum = 0; colNum < lastColNum; colNum++) { cell = row.getCell(colNum); if (cell == null) { sb.append("<td> </td>"); continue; } String stringValue = getCellValue(cell); if (map[0].containsKey(rowNum + "," + colNum)) { String pointString = map[0].get(rowNum + "," + colNum); map[0].remove(rowNum + "," + colNum); int bottomeRow = Integer.valueOf(pointString.split(",")[0]); int bottomeCol = Integer.valueOf(pointString.split(",")[1]); int rowSpan = bottomeRow - rowNum + 1; int colSpan = bottomeCol - colNum + 1; sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' "); } else if (map[1].containsKey(rowNum + "," + colNum)) { map[1].remove(rowNum + "," + colNum); continue; } else { sb.append("<td "); } HSSFCellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { short alignment = cellStyle.getAlignment(); sb.append("align='" + convertAlignToHtml(alignment) + "' "); short verticalAlignment = cellStyle.getVerticalAlignment(); sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' "); HSSFFont hf = cellStyle.getFont(wb); short boldWeight = hf.getBoldweight(); short fontColor = hf.getColor(); sb.append("style='"); HSSFPalette palette = wb.getCustomPalette(); HSSFColor hc = palette.getColor(fontColor); sb.append("font-weight:" + boldWeight + ";"); // System.out.println(hf.getFontHeight()); sb.append("font-size: " + hf.getFontHeight() / 2.5 + "%;"); String fontColorStr = convertToStardColor(hc); if (fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append("color:" + fontColorStr + ";"); } int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) ; sb.append("width:" + columnWidth + "px;"); short bgColor = cellStyle.getFillForegroundColor(); hc = palette.getColor(bgColor); String bgColorStr = convertToStardColor(hc); if (bgColorStr != null && !"".equals(bgColorStr.trim())) { sb.append("background-color:" + bgColorStr + ";"); } /* short borderColor = cellStyle.getBottomBorderColor(); hc = palette.getColor(borderColor); String borderColorStr = convertToStardColor(hc); if (borderColorStr != null && !"".equals(borderColorStr.trim())) { sb.append("border-color:" + borderColorStr + ";"); } */ sb.append( getBorderStyle(palette,0,cellStyle.getBorderTop(),cellStyle.getTopBorderColor())); sb.append( getBorderStyle(palette,1,cellStyle.getBorderRight(),cellStyle.getRightBorderColor())); sb.append( getBorderStyle(palette,3,cellStyle.getBorderLeft(),cellStyle.getLeftBorderColor())); sb.append( getBorderStyle(palette,2,cellStyle.getBorderBottom(),cellStyle.getBottomBorderColor())); // boolean borderBoolean = cellStyle.getWrapText(); // // if(borderBoolean){ // sb.append("border-style: inset;"); // } sb.append("' "); } sb.append(">"); if (stringValue == null || "".equals(stringValue.trim())) { sb.append(" "); } else { sb.append(stringValue.replace(String.valueOf((char) 160), " ")); } sb.append("</td>"); } sb.append("</tr>"); } sb.append("</table>"); return sb.toString(); } @SuppressWarnings("unchecked") private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) { Map<String, String> map0 = new HashMap<String, String>(); Map<String, String> map1 = new HashMap<String, String>(); int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress range = null; for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); int topRow = range.getFirstRow(); int topCol = range.getFirstColumn(); int bottomRow = range.getLastRow(); int bottomCol = range.getLastColumn(); map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); // System.out.println(topRow + "," + topCol + "," + bottomRow + "," // + bottomCol); int tempRow = topRow; while (tempRow <= bottomRow) { int tempCol = topCol; while (tempCol <= bottomCol) { map1.put(tempRow + "," + tempCol, ""); tempCol++; } tempRow++; } map1.remove(topRow + "," + topCol); } Map[] map = { map0, map1 }; return map; } private static String convertAlignToHtml(short alignment) { String align = "left"; switch (alignment) { case HSSFCellStyle.ALIGN_LEFT: align = "left"; break; case HSSFCellStyle.ALIGN_CENTER: align = "center"; break; case HSSFCellStyle.ALIGN_RIGHT: align = "right"; break; default: break; } return align; } private static String convertVerticalAlignToHtml(short verticalAlignment) { String valign = "middle"; switch (verticalAlignment) { case HSSFCellStyle.VERTICAL_BOTTOM: valign = "bottom"; break; case HSSFCellStyle.VERTICAL_CENTER: valign = "center"; break; case HSSFCellStyle.VERTICAL_TOP: valign = "top"; break; default: break; } return valign; } private static String convertToStardColor(HSSFColor hc) { StringBuffer sb = new StringBuffer(""); if (hc != null) { if (HSSFColor.AUTOMATIC.index == hc.getIndex()) { return null; } sb.append("#"); for (int i = 0; i < hc.getTriplet().length; i++) { sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); } } return sb.toString(); } private static String fillWithZero(String str) { if (str != null && str.length() < 2) { return "0" + str; } return str; } private static String getCellValue(HSSFCell cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: //DecimalFormat format = new DecimalFormat("#0.##"); //return format.format(cell.getNumericCellValue()); // return String.valueOf(cell.getNumericCellValue()); DecimalFormat format = new DecimalFormat(); //将数字显示为数字 // format.applyPattern("0.00%"); return format.format(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); // case HSSFCell.CELL_TYPE_FORMULA: // // return cell.getCellFormula(); default: return ""; } } static String[] bordesr={"border-top:","border-right:","border-bottom:","border-left:"}; static String[] borderStyles={"solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid","solid","solid","solid","solid"}; //tring[] borderClors={}; private static String getBorderStyle( HSSFPalette palette ,int b,short s, short t){ if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";; String borderColorStr = convertToStardColor( palette.getColor(t)); borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr; // System.out.println( bordesr[b]+borderStyles[s]+borderColorStr+" 1px; "+t); return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;"; } }