去年四月份解析EXCEL时从网上学到的一些东西,做了个小DEMO便放置在那里,计划重新开始更博,先贴一篇旧贴,待我慢慢更新。
1.需要JAR包
2.解释CVS DEMO 一个类搞定
package com.test;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class CSVAnalysis {
private InputStreamReader fr = null;
private BufferedReader br = null;
public CSVAnalysis(String f) throws
IOException {
fr = new InputStreamReader(new
FileInputStream(f));
}
public List> readCSVFile() throws IOException
{
br = new
BufferedReader(fr);
String rec = null;// 一行
String str;// 一个单元格
List> listFile = new
ArrayList>();
try {
// 读取一行
while ((rec =
br.readLine()) != null) {
Pattern
pCells = Pattern
.compile("(\"[^\"]*(\"{2})*[^\"]*\")*[^,]*,");
Matcher
mCells = pCells.matcher(rec + ",");
List
cells = new ArrayList();// 每行记录一个list
//
读取每个单元格
while
(mCells.find()) {
str
= mCells.group();
str
= str.replaceAll(
"(?sm)\"?([^\"]*(\"{2})*[^\"]*)\"?.*,",
"$1");
str
= str.replaceAll("(?sm)(\"(\"))", "$2");
cells.add(str);
}
listFile.add(cells);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fr !=
null) {
fr.close();
}
if (br !=
null) {
br.close();
}
}
return listFile;
}
public static void main(String[] args) throws
Throwable {
CSVAnalysis parser = new
CSVAnalysis("f:/tmp_category_level.csv");
List> list =
parser.readCSVFile();
List liststr =
list.get(1);
for (String string : liststr)
{
System.out.print(string
+ "\t");
}
}
}
3.解析EXCEL
需要前台写个JSP上传表单 看SERVLET代码你也懂的
类1
package com.servlet;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import
org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import com.util.ReadExcel;
public class ImportExcelServlet extends HttpServlet {
private static final long serialVersionUID =
1L;
// 缓冲区域
File tempPathFile;
// 默认路径
String uploadTo = "D:\\";
// 支持的文件类型
String[] errorType = { ".xls",".csv" };
// 格式化日期
SimpleDateFormat format = new
SimpleDateFormat("yyyyMMddHHmmssSSS");
@SuppressWarnings("unchecked")
public void doGet(HttpServletRequest req,
HttpServletResponse resp)
throws
ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
// 取得服务器真实路径
uploadTo =
req.getSession().getServletContext().getRealPath("\\")
+
"upload\\";
//
设置上传到服务器的路径你项目下的upload文件夹,如果没有则创建
tempPathFile = new
File(uploadTo);
if (!tempPathFile.exists())
{
tempPathFile.mkdirs();
}
DiskFileItemFactory factory
= new DiskFileItemFactory();
// 设置缓冲区大小,这里是4kb
factory.setSizeThreshold(4096);
// 设置缓冲区目录
factory.setRepository(tempPathFile);
// 创建一文件上传处理程序
ServletFileUpload upload = new
ServletFileUpload(factory);
// 设置最大文件尺寸,这里是4MB
upload.setSizeMax(10 * 1024 *
1024);
// 开始读取上传信息
List fileItems = new
ArrayList();
try {
fileItems =
upload.parseRequest(req);
} catch (FileUploadException
e1) {
e1.printStackTrace();
}
// 依次处理每个上传的文件
Iterator iter =
fileItems.iterator();
System.out.println("fileItems总数是"
+ fileItems.size());
// 正则匹配,过滤路径取文件名
String regExp =
".+\\\\(.+)$";
Pattern p =
Pattern.compile(regExp);
while (iter.hasNext()) {
FileItem item
= (FileItem) iter.next();
//
忽略其他不是文件域的所有表单信息
System.out.println("正在处理"
+ item.getFieldName());
if
(!item.isFormField()) {
String
name = item.getName();
long
size = item.getSize();
if
((name == null || name.equals("")) && size == 0)
continue;
Matcher
m = p.matcher(name);
boolean
result = m.find();
if
(result) {
boolean
flag = false;
for
(int temp = 0; temp < errorType.length; temp++) {
if
(m.group(1).endsWith(errorType[temp])) {
flag
= true;
}
}
if
(!flag) {
System.out.println("上传了不支持的文件类型");
throw
new IOException(name + ": wrong type");
}
try
{
String
fileName = uploadTo + format.format(new Date())
+
m.group(1).substring(m.group(1).indexOf("."));
item.write(new
File(fileName));
//
调用ReadExcel类进行读出excel
ReadExcel.readExcel(fileName,
resp.getWriter());
System.out.println(name
+ "\t\t文件大小为:" + size);
}
catch (Exception e) {
e.printStackTrace();
}
}
} else
{
//
这里添加对不是上传文件表单项的处理
System.out.println("这是一个表单项");
}
}
}
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws
ServletException, IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
PrintWriter out =
response.getWriter();
out
.println("");
doGet(request, response);
}
public void init() throws ServletException
{
// 上传时的缓存路径 可以任意设置
tempPathFile = new
File("D:\\apache-tomcat-6.0.20\\temp");
if (!tempPathFile.exists())
{
tempPathFile.mkdirs();
}
}
}
类2
package com.util;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcel {
public static void readExcel(String pathname,
PrintWriter out) {
try {
// 打开文件
Workbook book
= Workbook.getWorkbook(new File(pathname));
for (int t =
0; t < book.getSheets().length; t++) {
out.println("
第:" + (t + 1) + "个sheet内容为:
");
//
取得第i个sheet
Sheet
sheet = book.getSheet(t);
//
取得行数
int
rows = sheet.getRows();
for
(int i = 0; i < rows; i++) {
Cell[]
cell = sheet.getRow(i);
for
(int j = 0; j < cell.length; j++) {
//
getCell(列,行)
out.print(sheet.getCell(j,
i).getContents());
out.print(" ");
}
out.println("
");
}
}
// 关闭文件
book.close();
} catch (BiffException e)
{
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
抟摇直上 有些东西
忘记了就回过头来看下拾起曾经的记忆。2014年 07-08