主要是上传功能和下载功能,因为EXCEL版本的问题,所以由两种.xls和.xlsx两种格式,其中对Excel中的数据也进行了格式的检查,数据库名字为T_able,DownModelBean类我就不贴出来了,里面就是一些setter个getter方法。
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class UpDwServlet extends HttpServlet {
private static final String SPLIT = ",";
private static final Pattern HARF_ENUM = Pattern.compile("^[a-zA-Z0-9]+$");
private static final Pattern NUM_ENUM = Pattern.compile("^[0-9]{3}$");
private static final Pattern NUM_ENUM1 = Pattern.compile("^[0-9]{3}[.][0]$");
private static final Pattern DEL_ENUM = Pattern.compile("^[01]$");
private static final Pattern DATA_ENUM = Pattern.compile("^((?:19|20)[0-9][0-9])(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])$");
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException {
doPost(req, res);
}
public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException {
try {
String co = req.getParameter("co");
String fileName = req.getParameter("excelPath");
String POST_RETURN_URL = req.getParameter("url");
String POST_RETURN_URL1 = req.getParameter("url1");
String POST_RETURN_URL2 = req.getParameter("url2");
StringBuffer url = new StringBuffer(req.getContextPath());
if (co.equals("test")) {
String fileType = fileName.substring(fileName.length() - DefineFrame.NUM4, fileName.length());
List<String[]> list = new ArrayList<String[]>();
FileInputStream fs = new FileInputStream(fileName);
if (fileType.equals("xlsx")) {
list = getExcelXLSX(fs);
} else {
list = getExcelXLS(fs);
}
fs.close();
if (list.size() > 0) {
String errCheck = errCheck(list);
if (errCheck.length() > 1) {
req.getSession().setAttribute("checkFlag", errCheck);
url.append(POST_RETURN_URL2);
res.sendRedirect(url.toString());
} else {
excelInsert(list);
url.append(POST_RETURN_URL1);
res.sendRedirect(url.toString());
}
}
}
else if (fileName.equals("test")) {
CreateExcel(co);
url.append(POST_RETURN_URL);
res.sendRedirect(url.toString());
}
} catch (IOException e) { }
}
public List<String[]> getExcelXLSX(FileInputStream is) {
List<String[]> list = new ArrayList<String[]>();
try {
//找到导入的文件
XSSFWorkbook hwk = new XSSFWorkbook(is);
XSSFSheet sh = hwk.getSheetAt(0);
//得到行数
int rows = sh.getLastRowNum() + DefineFrame.NUM1 - sh.getFirstRowNum();
for (int i = 1; i < rows; i++) {
XSSFRow row = sh.getRow(i);
//得到列数
int cols = row.getLastCellNum() + DefineFrame.NUM1 - row.getFirstCellNum();
String[] str = new String[cols];
for (int j = 0; j < cols; j++) {
Object col = row.getCell((short) j);
Object colNext = row.getCell((short) (j + DefineFrame.NUM1));
if (col != null) {
str[j] = col.toString().trim();
} else {
if (colNext != null) {
Object colValue = "";
str[j] = colValue.toString().trim();
}
}
}
list.add(str);
}
} catch (IOException e) {
}
return list;
}
public List<String[]> getExcelXLS(FileInputStream is) {
List<String[]> list = new ArrayList<String[]>();
try {
//找到导入的文件
HSSFWorkbook hwk = new HSSFWorkbook(is);
HSSFSheet sh = hwk.getSheetAt(0);
//得到行数
int rows = sh.getLastRowNum() + DefineFrame.NUM1 - sh.getFirstRowNum();
for (int i = 1; i < rows; i++) {
HSSFRow row = sh.getRow(i);
//得到列数
int cols = row.getLastCellNum() + DefineFrame.NUM1 - row.getFirstCellNum();
String[] str = new String[cols];
for (int j = 0; j < cols; j++) {
Object col = row.getCell((short) j);
Object colNext = row.getCell((short) (j + DefineFrame.NUM1));
if (col != null) {
str[j] = col.toString().trim();
} else {
if (colNext != null) {
Object colValue = "";
str[j] = colValue.toString().trim();
}
}
}
list.add(str);
}
} catch (IOException e) { }
return list;
}
public static List<DownModelBean> QueryDown(String co) throws NamingException {
DBConnection con = null;
Statement st = null;
ResultSet rs = null;
String sql = "select * from T_able where CODE1 = " + "'" + co + "'";
con = new DBConnection();
List<DownModelBean> list = new ArrayList<DownModelBean>();
try {
Connection connect = con.connect();
st = connect.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
DownModelBean model = new DownModelBean();
model.setCODE1(rs.getString(DefineFrame.NUM1));
model.setCODE2(rs.getString(DefineFrame.NUM2));
model.setEFFECTIVE_BGN_DATE(rs.getString(DefineFrame.NUM3));
model.setCONTENT1(rs.getString(DefineFrame.NUM4));
model.setCONTENT2(rs.getString(DefineFrame.NUM5));
model.setCONTENT3(rs.getString(DefineFrame.NUM6));
model.setCONTENT4(rs.getString(DefineFrame.NUM7));
model.setSORT_ORDER(rs.getInt(DefineFrame.NUM8));
model.setDEL_FLG(rs.getString(DefineFrame.NUM9));
model.setLAST_UPDATE_USER_ID(rs.getString(DefineFrame.NUM10));
model.setLAST_UPDATE_DATE(rs.getString(DefineFrame.NUM11));
list.add(model);
}
connect.close();
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public void CreateExcel(String co) {
HSSFWorkbook workBook = new HSSFWorkbook();
//创建一个工作表,名为co
HSSFSheet sheet = workBook.createSheet(co);
//设置单元格的宽度
sheet.setColumnWidth((short) DefineFrame.NUM0, DefineFrame.NUM2500);
sheet.setColumnWidth((short) DefineFrame.NUM1, DefineFrame.NUM2500);
sheet.setColumnWidth((short) DefineFrame.NUM2, DefineFrame.NUM6000);
sheet.setColumnWidth((short) DefineFrame.NUM3, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM4, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM5, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM6, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM7, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM8, DefineFrame.NUM3500);
sheet.setColumnWidth((short) DefineFrame.NUM9, DefineFrame.NUM6000);
sheet.setColumnWidth((short) DefineFrame.NUM10, DefineFrame.NUM6000);
//创建一个单元格
HSSFRow row = sheet.createRow((short) 0);
//构造一个数组设置第一行之后的单元格
HSSFCell cell[] = new HSSFCell[11];
for (int i = 0; i < 11 ; i ++ ) {
cell[i] = row.createCell(i);
}
cell[0].setCellValue("CODE1");
cell[1].setCellValue("CODE2");
cell[2].setCellValue("EFFECTIVE_BGN_DATE");
cell[3].setCellValue("CONTENT1");
cell[4].setCellValue("CONTENT2");
cell[5].setCellValue("CONTENT3");
cell[6].setCellValue("CONTENT4");
cell[7].setCellValue("SORT_ORDER");
cell[8].setCellValue("DEL_FLG");
cell[9].setCellValue("LAST_UPDATE_USER_ID");
cell[10].setCellValue("LAST_UPDATE_DATE");
List<DownModelBean> list = null;
try {
//获得从数据库中查询出来的数据
list = UpDwServlet.QueryDown(co);
} catch (NamingException e1) {
}
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i ++ ) {
DownModelBean model = list.get(i);
HSSFRow dataRow = sheet.createRow( i + 1);
HSSFCell data[] = new HSSFCell[11];
for (int j = 0;j < 11; j++ ) {
data[j] = dataRow.createCell(j);
}
data[0].setCellValue(model.getCODE1());
data[1].setCellValue(model.getCODE2());
data[2].setCellValue(model.getEFFECTIVE_BGN_DATE());
data[3].setCellValue(model.getCONTENT1());
data[4].setCellValue(model.getCONTENT2());
data[5].setCellValue(model.getCONTENT3());
data[6].setCellValue(model.getCONTENT4());
data[7].setCellValue(model.getSORT_ORDER());
data[8].setCellValue(model.getDEL_FLG());
data[9].setCellValue(model.getLAST_UPDATE_USER_ID());
data[10].setCellValue(model.getLAST_UPDATE_DATE());
try {
File file = new File("D:\\" + co + ".xls");
FileOutputStream fos = new FileOutputStream(file);
workBook.write(fos);
fos.close(); }
catch (FileNotFoundException e) { } catch (IOException e) { }
}
}
}
public String errCheck(List<String[]> list) {
String errCheck = "";
String[] str1 = null;
for (int i = 0;i < list.size();i ++ ){
str1 = list.get(i);
if (str1[0].length() > DefineFrame.NUM5 || !HARF_ENUM.matcher(str1[0]).matches()) {
int m =i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (str1[1].length() > DefineFrame.NUM30 || !HARF_ENUM.matcher(str1[1]).matches()) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (str1[2].length() > DefineFrame.NUM30 || !DATA_ENUM.matcher(str1[2]).matches()) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (str1[3].length() > DefineFrame.NUM30) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (str1[4].length() > DefineFrame.NUM30) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (str1[5].length() > DefineFrame.NUM30) {
int m=i+1;
errCheck = errCheck +m+"行:有错误\n";
}
if (str1[6].length() > DefineFrame.NUM30)
{
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if (!NUM_ENUM1.matcher(str1[7]).matches() && !NUM_ENUM.matcher(str1[7]).matches()) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
if(!DEL_ENUM.matcher(str1[8]).matches()) {
int m = i + 1;
errCheck = errCheck + m + "行:有错误\n";
}
}
return errCheck;
}
public void excelInsert(List<String[]> list) {
try {
DBConnection con = new DBConnection();
Connection connect = null;
connect = con.connect();
PreparedStatement psta = null;
for (int i = 0; i < list.size(); i ++) {
String st = "";
String[] str = null;
//获得Excel中的数据
str = list.get(i);
st = "INSERT INTO T_able(CODE1, CODE2, EFFECTIVE_BGN_DATE, CONTENT1, CONTENT2, CONTENT3, CONTENT4, SORT_ORDER, DEL_FLG, LAST_UPDATE_USER_ID, LAST_UPDATE_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
psta = connect.prepareStatement(st);
connect.setAutoCommit(false);
psta.setObject(1, str[0]);
psta.setObject(2, str[1]);
psta.setObject(3, str[2]);
psta.setObject(4, str[3]);
psta.setObject(5, str[4]);
psta.setObject(6, str[5]);
psta.setObject(7, str[6]);
psta.setObject(8, str[7]);
psta.setObject(9, str[8]);
psta.setObject(10, str[9]);
psta.setObject(11, str[10]);
LogMgr.log(LogMgr.INFO, "【数据检查】 ," +
str[0] + SPLIT +
str[1] + SPLIT +
str[2] + SPLIT +
str[3] + SPLIT +
str[4] + SPLIT +
str[5] + SPLIT +
str[6] + SPLIT +
str[7] + SPLIT +
str[8] + SPLIT +
str[9] + SPLIT +
str[10] );
//执行插入
int s = psta.executeUpdate();
connect.commit();
if (psta != null) {
psta.close();
}
}
connect.close();
} catch (SQLException e) { } catch (NamingException e) { }
}
}