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;";
}
}
