package cn.gut.toiawasethema.servlet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import javax.servlet.RequestDispatcher;
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.FileUpload;
import org.apache.commons.fileupload.RequestContext;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.servlet.ServletRequestContext;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import cn.gut.toiawasethema.Utils.DBConn;
import cn.gut.toiawasethema.dao.FilePathDAO;
import cn.gut.toiawasethema.dao.csvReadToDBDao;
import cn.gut.toiawasethema.javabean.Textarea;
@SuppressWarnings("serial")
public class CRTDBSERVLET extends HttpServlet {
private int ERRORpage = 0;
private int FieldId = 0;
private String FieldNUM = "";
private int FieldCount = 0;
private File isDir;
private String SqlTemp = new String("");
private String zhidingPath;
private String readPath;
private Textarea textarea;
private csvReadToDBDao csvdao;
private String SQLinsert;
private String SQLSet;
private String SQLdata;
private String SQLupdate;
private String SQLquery;
private String yyStringType[] = new String[40];
private String riString[] = new String[40];
private String yyString[] = new String[40];
private StringBuffer filename;
private String SQLqueryCount;
public void csvReadToDBSQL(String SqlTemp) {
SQLinsert = "INSERT INTO rx_toiawase_kanri(" + SqlTemp + ") VALUE(";
SQLupdate = "UPDATE rx_toiawase_kanri SET ";
SQLquery = "SELECT kanri_no FROM rx_toiawase_kanri WHERE kanri_no=";
SQLqueryCount = "SELECT kanri_no FROM rx_toiawase_kanri";
}
@SuppressWarnings({ "rawtypes", "unchecked", "unused" })
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// TODO Auto-generated method stub
String PriKey = "";
int DateType = 0;
int IntType = 0;
int InsertID = 0;
String[] SqlTempType = null;
csvdao = new csvReadToDBDao();
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
// database fields type
yyStringType[0] = "";
yyStringType[1] = "";
yyStringType[2] = "";
yyStringType[3] = "";
yyStringType[4] = "";
yyStringType[5] = "";
yyStringType[6] = "date";
yyStringType[7] = "";
yyStringType[8] = "blob";
yyStringType[9] = "date";
yyStringType[10] = "date";
yyStringType[11] = "";
yyStringType[12] = "blob";
yyStringType[13] = "blob";
yyStringType[14] = "";
yyStringType[15] = "date";
yyStringType[16] = "";
yyStringType[17] = "";
yyStringType[18] = "";
yyStringType[19] = "blob";
yyStringType[20] = "blob";
yyStringType[21] = "blob";
yyStringType[22] = "blob";
yyStringType[23] = "blob";
yyStringType[24] = "";
yyStringType[25] = "";
yyStringType[26] = "";
yyStringType[27] = "int";
yyStringType[28] = "blob";
yyStringType[29] = "blob";
yyStringType[30] = "";
yyStringType[31] = "";
yyStringType[32] = "";
yyStringType[33] = "";
yyStringType[34] = "";
yyStringType[35] = "";
yyStringType[36] = "date";
yyStringType[37] = "";
yyStringType[38] = "date";
yyStringType[39] = "";
// 字段保存
riString[0] = "管理No";
riString[1] = "参照管理No";
riString[2] = "環境";
riString[3] = "会社";
riString[4] = "ユーザー";
riString[5] = "問合せ分類";
riString[6] = "問合せ日";
riString[7] = "問合せ者";
riString[8] = "問合せ内容";
riString[9] = "回答期限";
riString[10] = "回答日";
riString[11] = "回答者";
riString[12] = "回答内容";
riString[13] = "RX対応内容";
riString[14] = "ステータス";
riString[15] = "本番リリース予定日";
riString[16] = "対客ステータス";
riString[17] = "Alcyone番号";
riString[18] = "類似調査担当";
riString[19] = "類似調査結果";
riString[20] = "他社影響有無";
riString[21] = "備考1";
riString[22] = "備考2";
riString[23] = "備考3";
riString[24] = "調査担当";
riString[25] = "UAT障害";
riString[26] = "問合せ作業除外ステータス";
riString[27] = "データ補正要否";
riString[28] = "データ補正確認者";
riString[29] = "調査結果ファイル";
riString[30] = "問合せ者分類";
riString[31] = "テーマ調整No";
riString[32] = "開発テーマNo";
riString[33] = "同件障害No";
riString[34] = "関連障害No";
riString[35] = "完了日";
riString[36] = "登录日期";
riString[37] = "登录者";
riString[38] = "更新日期";
riString[39] = "更新者";
// 数据库字段名
yyString[0] = "kanri_no";
yyString[1] = "sansyou_kanri_no";
yyString[2] = "kankyou";
yyString[3] = "kaisya";
yyString[4] = "user";
yyString[5] = "toiawase_group";
yyString[6] = "toiawase_date";
yyString[7] = "toiawase_man";
yyString[8] = "toiawase_content";// blob
yyString[9] = "kaitou_limit";
yyString[10] = "kaitou_date";
yyString[11] = "kaitou_man";
yyString[12] = "kaitou_content";// blob
yyString[13] = "Rxtaiou_content";// blob
yyString[14] = "status";
yyString[15] = "honban_release_date";
yyString[16] = "taikyaku_status";
yyString[17] = "Alcyone_no";
yyString[18] = "ruiji_tyosa_man";
yyString[19] = "ruiji_tyosa_result";// blob
yyString[20] = "tasya_eikyou";// blob
yyString[21] = "biko1";// blob
yyString[22] = "biko2";// blob
yyString[23] = "biko3";// blob
yyString[24] = "tyosa_man";
yyString[25] = "UATsyougai";
yyString[26] = "toiawase_igai_status";
yyString[27] = "data_hosei";
yyString[28] = "data_hosei_man";// blob
yyString[29] = "tyosa_result_fairu";// blob
yyString[30] = "toiawasesya_group";
yyString[31] = "thema_tyousei_no";
yyString[32] = "kaihatu_teema_no";
yyString[33] = "douken_syougai_no";
yyString[34] = "kanren_syougai_no";
yyString[35] = "kanryou_date";
yyString[36] = "login_date";// 登录日期
yyString[37] = "loginer"; // 登录者
yyString[38] = "update_date"; // 更新日期
yyString[39] = "update_user";// 更新者
// 参数获取
StringBuffer reqstringString = new StringBuffer(
req.getParameter("filell"));
filename = reqstringString;
System.out.println("pathreq = " + reqstringString);
// 制定路径
InputStream is = null;
is = getClass().getClassLoader().getResourceAsStream(
"property.properties");
Properties p = new Properties();
try {
p.load(is);
} catch (IOException e1) {
e1.printStackTrace();
}
zhidingPath = (String) p.get("loadPath");
// 正则表达式
FieldNUM = (String) p.get("FieldNUM");
DBConn dbConn = new DBConn();
// 运输通道
isDir = new File(zhidingPath.substring(0, zhidingPath.length() - 1));
if (!(isDir.isDirectory())) {
isDir.mkdirs();
}
File file = new File(zhidingPath);
if ((zhidingPath.indexOf("/", zhidingPath.length() - 1) >= 0)
|| (zhidingPath.indexOf("\\", zhidingPath.length() - 1) >= 0)
&& !file.isDirectory()) {
RequestContext responsecContext = new ServletRequestContext(req);
// System.out.println(FileUpload.isMultipartContent(responsecContext));
if (FileUpload.isMultipartContent(responsecContext)) {
DiskFileItemFactory factory = new DiskFileItemFactory();
factory.setRepository(new File(zhidingPath));
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setSizeMax(100 * 1024 * 1024);
List items = new ArrayList();
try {
items = upload.parseRequest(req);
} catch (Exception e) {
e.printStackTrace();
}
Iterator iterator = items.iterator();
while (iterator.hasNext()) {
FileItem fileItem = (FileItem) iterator.next();
if (fileItem.isFormField()) {
// System.out.println(fileItem.getFieldName() + " "
// + fileItem.getName() + " " + " "
// + new String(fileItem.getString().getBytes()));
} else {
System.out.println(fileItem.getFieldName() + " "
+ fileItem.getName() + " " + " "
+ fileItem.isInMemory() + " "
+ fileItem.getContentType() + " "
+ fileItem.getSize());
if (fileItem.getName() != null
&& fileItem.getSize() != 0) {
File fullFile = new File(fileItem.getName());
File newfFile = new File(zhidingPath
+ fullFile.getName());
try {
fileItem.write(newfFile);
} catch (Exception e) {
e.printStackTrace();
}
textarea = new Textarea();
textarea.setContentString(zhidingPath);
FilePathDAO pathDAO = new FilePathDAO(textarea);
if (pathDAO.getResult() != 0) {
System.out.println("-----CSVPATH "
+ zhidingPath + reqstringString
+ " insert success!");
} else {
System.out.println("-----CSVPATHinsert falis!");
}
} else {
System.out.println("文件没有选择 或 文件内容为空");
resp.getWriter().write("TEXT IS NOT OR IS NULL!");
}
}
}
System.out.println("UPLOAD SECCUSS ！！");
}
}
// 运输结束
// 获取路径
readPath = zhidingPath + filename;
int returnwhile = 0;
while (readPath != "" && !filename.equals("")) {
// TO DB
if (returnwhile != 0) {
break;
}
returnwhile += 1;
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(new FileInputStream(readPath));
HSSFSheet sheet = wb.getSheetAt(0);
int i = sheet.getLastRowNum();
for (int k = 0; k <= i; k++) {
HSSFRow row = sheet.getRow(k);
if (k == 0) {
// 第一行
for (int j = 0; j < row.getLastCellNum(); j++) {// 获取查询字段名
if ((row.getCell((short) j)).equals("管理No")) {
PriKey = yyString[0];
FieldId = i;
}
for (int m = 0; m < 40; j++) {
if ((row.getCell((short) j))
.equals(riString[m])) {
FieldCount += 1;
SqlTemp += yyString[m] + ",";
if (yyStringType[m].equals("date")) {// datatype
// data
DateType = m;
}
if (yyStringType[m].equals("int")) {// inttype
// data
IntType = m;
}
}
}
}
if (!(SqlTemp.toString().split(","))[0].equals("")) {
SqlTemp = SqlTemp
.substring(0, SqlTemp.length() - 1);
} else {
System.out.println("字段名IsNULL!");
break;
}
System.out.println("SqlTemp==" + SqlTemp);
SqlTempType = SqlTemp.split(",");
} else {
for (int j = 0; j < SqlTempType.length; j++) {
SqlTemp += SqlTempType[j] + ",";
}
SqlTemp = SqlTemp.substring(0, SqlTemp.length()-1);
this.csvReadToDBSQL(SqlTemp);
for (int j = 0; j < row.getLastCellNum(); j++) {// 数据保存
if (DateType == j
&& (row.getCell((short) j).toString())
.equals("")) {
this.SQLdata += "null,";
this.SQLSet += SqlTemp.toString().split(",")[j]
+ "= null,";
} else if (IntType == j
&& (row.getCell((short) j).toString())
.equals("")) {
this.SQLdata += "null,";
this.SQLSet += SqlTemp.toString().split(",")[j]
+ "=null,";
} else {
this.SQLdata += "'"
+ row.getCell((short) j).toString()
.replace("'", "、") + "',";
this.SQLSet += SqlTemp.toString().split(",")[j]
+ "='"
+ row.getCell((short) j).toString()
.replace("'", "、") + "',";
}
}
this.SQLdata = this.SQLdata.substring(0,
this.SQLdata.length() - 1);
this.SQLSet = this.SQLSet.substring(0,
this.SQLSet.length() - 1);
// next data to db
for (int j = 0; j < row.getLastCellNum(); j++) {// 数据行
// 判断插入、更新
if (PriKey.toString().equals("管理No")) {// 更新
// 查询ID判断更新、插入data
boolean returnQuery = false;
try {
returnQuery = csvdao.Query( SQLquery + "'"+row.getCell((short) j).toString()+"'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// ID已存在更新数据
if (returnQuery) {
int success = 0;
try {
success = csvdao.UPdate(SQLupdate + this.SQLSet + "where " + PriKey + "='" + row.getCell((short) FieldId).toString() + "'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (success == 0) {
System.out.println("语句更新失败："+ SQLupdate + this.SQLSet + "where " + PriKey + "='" + row.getCell((short) FieldId).toString() + "'");
} else {
System.out.println("语句更新成功！");
}
}
// ID空 插入数据
if (!returnQuery) {
int success = 0;
try {
success = csvdao.UPdate(SQLinsert + this.SQLinsert + this.SQLdata);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (success == 0) {
System.out.println("语句插入失败："+ SQLinsert + this.SQLinsert + this.SQLdata);
} else {
System.out.println("语句插入成功！");
}
}
} else {
// 自动增加ID插入data
// InsertID
Boolean QUERYid = false;
try {
QUERYid = csvdao.Query(SQLqueryCount + " where kanri_no='" + (InsertID+1) + "'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
while(QUERYid){
InsertID += 1;
try {
QUERYid = csvdao.Query(SQLqueryCount + " where kanri_no='" + (InsertID+1) + "'");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
int success = 0;
try {
success = csvdao.UPdate("INSERT INTO rx_toiawase_kanri(" +"kanri_no," + SqlTemp + ") VALUE(" + SQLdata + ")");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (success == 0) {
System.out.println("语句插入失败："+ "INSERT INTO rx_toiawase_kanri(" +"kanri_no," + SqlTemp + ") VALUE(" + SQLdata + ")");
} else {
System.out.println("没 ID 语句插入成功！");
}
}
}
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 删除目录
if (deleteDir(new File(readPath))) {
System.out.println("成功删除文件。。");
} else {
System.out.println("删除文件失败 ！！");
}
req.getSession().setAttribute("alert", "true");
RequestDispatcher wm = req.getRequestDispatcher("CSV読み込み.jsp");
wm.forward(req, resp);
}
private static boolean deleteDir(File dir) {
if (dir.isDirectory()) {
String[] children = dir.list();
for (int i = 0; i < children.length; i++) {
boolean success = deleteDir(new File(dir, children[i]));
if (!success) {
return false;
}
}
}
return dir.delete();
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(req, resp);
}
}
String filepath = "d:excela.xls";
HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(filepath));
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
int i = hssfsheet.getLastRowNum();
for(int k=0;k<=i;k++)
{
HSSFRow hssfrow = hssfsheet.getRow(k);
for(int j=0;j<hssfrow.getLastCellNum();j++)
{
HSSFCell hssfcell = hssfrow.getCell((short)j);
System.out.println(hssfcell.getDateCellValue());
}
}
下载POI OFFICE 2007
http://download.csdn.net/detail/woo_1986/1587210
public class WriteToCSVServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String path = "D:/datacsv.csv";
// 服务器绝对路径
// 检查文件是否存在
String fileName = "datacsv.csv";
File obj = new File(path);
if (!obj.exists()) {
resp.setContentType("text/html;charset=GBK");
resp.getWriter().print("指定文件不存在！");
return;
}
// 读取文件名：用于设置客户端保存时指定默认文件名
// 写流文件到前端浏览器
ServletOutputStream out = resp.getOutputStream();
resp.setHeader("Content-disposition", "attachment;filename="
+ fileName);
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(new FileInputStream(path));
bos = new BufferedOutputStream(out);
byte[] buff = new byte[1024];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
System.out.println(bytesRead);
}
} catch (IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="javax.swing.JFileChooser;"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<link rel="stylesheet" href="css/Css.css" type="text/css" />
<script language="javascript" defer="defer" type="text/javascript"
src="js/js.js"></script>
<%
//数据库数据
String stringOptionString = "";
if (!(request.getAttribute("resultSelect").toString()).equals("")) {
stringOptionString = request.getAttribute("resultSelect")
.toString();
}
//字段头
String stringFieldGet = request.getAttribute("stringFieldGet")
.toString();
System.out.println(stringFieldGet + " stringFieldGet");
%>
</head>
<body>
<strong>
RXチームの問合せ一覧 >> CSVファイルに書き出す検索 >> CSVファイルに書き出す結果<br />
</strong>
<label><br /> <span class="style3">CSVファイルに書き出される内容は、次のとおりです</span><br />
<textarea name="textfield" style="width: 1260px; height: 130px;"><%=stringFieldGet%>
<%=stringOptionString%></textarea> </label>
<form id="form1" name="form1" method="post" action="WriteToCSVServlet">
<p>
<a href="CSVファイルに書き出す検索.jsp"><input type="button" name="button"
value="<<前の画面へ戻る" /></a> <input type="submit" name="submit"
value="書き出す" /> <input type="reset" name="reset" value="キャンセル" />
</p>
</form>
</body>
</html>
package cn.gut.toiawasethema.servlet;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.StringTokenizer;
import javax.servlet.RequestDispatcher;
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import cn.gut.toiawasethema.Utils.DBConn;
import cn.gut.toiawasethema.javabean.Textarea;
@SuppressWarnings("serial")
public class Log_Servlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
System.out.println((new SimpleDateFormat("yyyy-mm-dd hh-ss-mm"))
.format(new java.sql.Date(System.currentTimeMillis())));
String riSendString[] = new String[36];
String riString[] = new String[36];
String yyString[] = new String[36];
String StringBlob[] = new String[36];
int yyStringValue[] = new int[36];
String selectString = "";
int count = 0;
StringBuffer string = null;
// 所有字段统计
String stringSend = "";
String[] selectarrString = new String[36];
String sql = "select ";
int countReference = 0;
int j = 0; // 统计字段
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
// TODO Auto-generated method stub
// 设置页面条件
riString[0] = "管理No";
riString[1] = "参照管理No";
riString[2] = "環境";
riString[3] = "会社";
riString[4] = "ユーザー";
riString[5] = "問合せ分類";
riString[6] = "問合せ日";
riString[7] = "問合せ者";
riString[8] = "問合せ内容";
riString[9] = "回答期限";
riString[10] = "回答日";
riString[11] = "回答者";
riString[12] = "回答内容";
riString[13] = "RX対応内容";
riString[14] = "ステータス";
riString[15] = "";
riString[16] = "対客ステータス";
riString[17] = "Alcyone番号";
riString[18] = "類似調査担当";
riString[19] = "類似調査結果";
riString[20] = "他社影響有無";
riString[21] = "備考1";
riString[22] = "備考2";
riString[23] = "備考3";
riString[24] = "調査担当";
riString[25] = "UAT障害";
riString[26] = "問合せ作業除外ステータス";
riString[27] = "データ補正要否";
riString[28] = "データ補正確認者";
riString[29] = "調査結果ファイル";
riString[30] = "問合せ者分類";
riString[31] = "テーマ調整No";
riString[32] = "開発テーマNo";
riString[33] = "同件障害No";
riString[34] = "関連障害No";
riString[35] = "完了日";
// 设置数据库字段名
yyString[0] = "kanri_no";
yyString[1] = "sansyou_kanri_no";
yyString[2] = "kankyou";
yyString[3] = "kaisya";
yyString[4] = "user";
yyString[5] = "toiawase_group";
yyString[6] = "toiawase_date";
yyString[7] = "toiawase_man";
yyString[8] = "toiawase_content";// blob
yyString[9] = "kaitou_limit";
yyString[10] = "kaitou_date";
yyString[11] = "kaitou_man";
yyString[12] = "kaitou_content";// blob
yyString[13] = "Rxtaiou_content";// blob
yyString[14] = "status";
yyString[15] = "";
yyString[16] = "taikyaku_status";
yyString[17] = "Alcyone_no";
yyString[18] = "ruiji_tyosa_man";
yyString[19] = "ruiji_tyosa_result";// blob
yyString[20] = "tasya_eikyou";// blob
yyString[21] = "biko1";// blob
yyString[22] = "biko2";// blob
yyString[23] = "biko3";// blob
yyString[24] = "tyosa_man";
yyString[25] = "UATsyougai";
yyString[26] = "toiawase_igai_status";
yyString[27] = "data_hosei";
yyString[28] = "data_hosei_man";// blob
yyString[29] = "tyosa_result_fairu";// blob
yyString[30] = "toiawasesya_group";
yyString[31] = "thema_tyousei_no";
yyString[32] = "kaihatu_teema_no";
yyString[33] = "douken_syougai_no";
yyString[34] = "kanren_syougai_no";
yyString[35] = "kanryou_date";
// 类型判断
StringBlob[0] = "";
StringBlob[1] = "";
StringBlob[2] = "";
StringBlob[3] = "";
StringBlob[4] = "";
StringBlob[5] = "";
StringBlob[6] = "Date";
StringBlob[7] = "";
StringBlob[8] = "Blob";// blob
StringBlob[9] = "Date";
StringBlob[10] = "Date";
StringBlob[11] = "";
StringBlob[12] = "Blob";// blob
StringBlob[13] = "Blob";// blob
StringBlob[14] = "";
StringBlob[15] = "";
StringBlob[16] = "";
StringBlob[17] = "";
StringBlob[18] = "";
StringBlob[19] = "Blob";// blob
StringBlob[20] = "Blob";// blob
StringBlob[21] = "Blob";// blob
StringBlob[22] = "Blob";// blob
StringBlob[23] = "Blob";// blob
StringBlob[24] = "";
StringBlob[25] = "";
StringBlob[26] = "";
StringBlob[27] = "";
StringBlob[28] = "Blob";// blob
StringBlob[29] = "Blob";// blob
StringBlob[30] = "";
StringBlob[31] = "";
StringBlob[32] = "";
StringBlob[33] = "";
StringBlob[34] = "";
StringBlob[35] = "";
// 获取条件
selectString = (req.getParameter("OptionValue")).toString();
StringTokenizer sTokenizer = new StringTokenizer(selectString, ",");
// 显示查询条件
// 分割条件
// System.out.println(selectString);
int fieldsC = 0;
while (sTokenizer.hasMoreTokens()) {
// 页面值分割的获取
System.out.println(j);
selectarrString[count] = "" + sTokenizer.nextToken();
for (int i = 0; i < 36; i++) {
if ((selectarrString[count].trim().toString())
.equals(riString[i].trim().toString())) {
if ((selectarrString[count].trim().toString())
.equals("完了日")) {
// sql += yyString[i].trim() + "='" +
// selectarrString[count].trim()
// + "' and ";
yyStringValue[j] = i;
countReference = i;
riSendString[j] = riString[i];
} else {
yyStringValue[j] = i;
sql += yyString[i].trim() + ",";
riSendString[j] = riString[i];
}
}
}
j++;
count++;
}
// 字段名
for (int w = 0; w < j; w++) {
if (riSendString[w]!=null) {
fieldsC += 1;
stringSend += riSendString[w] + ",";
}
}
// System.out.println(j);
// 操作结果
DBConn dbConn = new DBConn();
sql = sql.substring(0, sql.length() - 1);
try {
if (countReference == 0) {
sql += " from rx_toiawase_kanri";
} else {
// 完了日SQL操作
sql += "," + yyString[35].trim()
+ " from rx_toiawase_kanri,thema_kanri";
}
System.out.println(sql);
string = new StringBuffer();
ResultSet rsResultSet = dbConn.executeQuery(sql);
System.out.println((new SimpleDateFormat("yyyy-mm-dd hh-ss-mm"))
.format(new java.sql.Date(System.currentTimeMillis())));
while (rsResultSet.next()) {
for (int i = 0; i < j; i++) {
if (StringBlob[yyStringValue[i]].equals("Blob")) {
if (rsResultSet.getBlob(yyString[yyStringValue[i]]) != null) {
string.append(Textarea
.getContentString(
(rsResultSet
.getBlob(yyString[yyStringValue[i]])))
.substring(
4,
(Textarea
.getContentString((rsResultSet
.getBlob(yyString[yyStringValue[i]]))))
.length())
+ ",");
}
} else {
string.append(rsResultSet
.getObject(yyString[yyStringValue[i]]) + ",");
}
}
}
System.out.println((new SimpleDateFormat("yyyy-mm-dd hh-ss-mm"))
.format(new java.sql.Date(System.currentTimeMillis())));
// req.setAttribute("resultSelect", (string.replaceAll("null",
// " ")).replaceAll("ll", " "));
req.setAttribute("fieldsC", fieldsC);
req.setAttribute("resultSelect", string);
req.setAttribute("stringFieldGet",
stringSend.substring(0, stringSend.length() - 1));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("e.." + e.getMessage());
}
datacsvpath(req, resp,fieldsC,stringSend);
RequestDispatcher wm = req.getRequestDispatcher("CSVファイルに書き出す結果.jsp");
wm.forward(req, resp);
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public void datacsvpath(HttpServletRequest req, HttpServletResponse resp,int fieldsCc,String stringFieldGet) throws FileNotFoundException{
System.out.println((new SimpleDateFormat("yyyy-mm-dd hh-ss-mm"))
.format(new java.sql.Date(System.currentTimeMillis())));
String riSendString[] = new String[36];
String riString[] = new String[36];
String yyString[] = new String[36];
String StringBlob[] = new String[36];
int yyStringValue[] = new int[36];
String selectString = "";
StringBuffer string = null;
int count = 0;
// 所有字段统计
String[] selectarrString = new String[36];
String sql = "select ";
int countReference = 0;
int j = 0; // 统计字段
try {
req.setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
resp.setCharacterEncoding("UTF-8");
// TODO Auto-generated method stub
// 设置页面条件
riString[0] = "管理No";
riString[1] = "参照管理No";
riString[2] = "環境";
riString[3] = "会社";
riString[4] = "ユーザー";
riString[5] = "問合せ分類";
riString[6] = "問合せ日";
riString[7] = "問合せ者";
riString[8] = "問合せ内容";
riString[9] = "回答期限";
riString[10] = "回答日";
riString[11] = "回答者";
riString[12] = "回答内容";
riString[13] = "RX対応内容";
riString[14] = "ステータス";
riString[15] = "";
riString[16] = "対客ステータス";
riString[17] = "Alcyone番号";
riString[18] = "類似調査担当";
riString[19] = "類似調査結果";
riString[20] = "他社影響有無";
riString[21] = "備考1";
riString[22] = "備考2";
riString[23] = "備考3";
riString[24] = "調査担当";
riString[25] = "UAT障害";
riString[26] = "問合せ作業除外ステータス";
riString[27] = "データ補正要否";
riString[28] = "データ補正確認者";
riString[29] = "調査結果ファイル";
riString[30] = "問合せ者分類";
riString[31] = "テーマ調整No";
riString[32] = "開発テーマNo";
riString[33] = "同件障害No";
riString[34] = "関連障害No";
riString[35] = "完了日";
// 设置数据库字段名
yyString[0] = "kanri_no";
yyString[1] = "sansyou_kanri_no";
yyString[2] = "kankyou";
yyString[3] = "kaisya";
yyString[4] = "user";
yyString[5] = "toiawase_group";
yyString[6] = "toiawase_date";
yyString[7] = "toiawase_man";
yyString[8] = "toiawase_content";// blob
yyString[9] = "kaitou_limit";
yyString[10] = "kaitou_date";
yyString[11] = "kaitou_man";
yyString[12] = "kaitou_content";// blob
yyString[13] = "Rxtaiou_content";// blob
yyString[14] = "status";
yyString[15] = "";
yyString[16] = "taikyaku_status";
yyString[17] = "Alcyone_no";
yyString[18] = "ruiji_tyosa_man";
yyString[19] = "ruiji_tyosa_result";// blob
yyString[20] = "tasya_eikyou";// blob
yyString[21] = "biko1";// blob
yyString[22] = "biko2";// blob
yyString[23] = "biko3";// blob
yyString[24] = "tyosa_man";
yyString[25] = "UATsyougai";
yyString[26] = "toiawase_igai_status";
yyString[27] = "data_hosei";
yyString[28] = "data_hosei_man";// blob
yyString[29] = "tyosa_result_fairu";// blob
yyString[30] = "toiawasesya_group";
yyString[31] = "thema_tyousei_no";
yyString[32] = "kaihatu_teema_no";
yyString[33] = "douken_syougai_no";
yyString[34] = "kanren_syougai_no";
yyString[35] = "kanryou_date";
// 类型判断
StringBlob[0] = "";
StringBlob[1] = "";
StringBlob[2] = "";
StringBlob[3] = "";
StringBlob[4] = "";
StringBlob[5] = "";
StringBlob[6] = "Date";
StringBlob[7] = "";
StringBlob[8] = "Blob";// blob
StringBlob[9] = "Date";
StringBlob[10] = "Date";
StringBlob[11] = "";
StringBlob[12] = "Blob";// blob
StringBlob[13] = "Blob";// blob
StringBlob[14] = "";
StringBlob[15] = "";
StringBlob[16] = "";
StringBlob[17] = "";
StringBlob[18] = "";
StringBlob[19] = "Blob";// blob
StringBlob[20] = "Blob";// blob
StringBlob[21] = "Blob";// blob
StringBlob[22] = "Blob";// blob
StringBlob[23] = "Blob";// blob
StringBlob[24] = "";
StringBlob[25] = "";
StringBlob[26] = "";
StringBlob[27] = "";
StringBlob[28] = "Blob";// blob
StringBlob[29] = "Blob";// blob
StringBlob[30] = "";
StringBlob[31] = "";
StringBlob[32] = "";
StringBlob[33] = "";
StringBlob[34] = "";
StringBlob[35] = "";
// 获取条件
// selectString = (String) (req.getSession()
// .getAttribute("stringFieldGet"));
selectString = stringFieldGet;
StringTokenizer sTokenizer = new StringTokenizer(selectString, ",");
while (sTokenizer.hasMoreTokens()) {
// 页面值分割的获取
System.out.println(j);
selectarrString[count] = "" + sTokenizer.nextToken();
for (int i = 0; i < 36; i++) {
if ((selectarrString[count].trim().toString())
.equals(riString[i].trim().toString())) {
if ((selectarrString[count].trim().toString())
.equals("完了日")) {
// sql += yyString[i].trim() + "='" +
// selectarrString[count].trim()
// + "' and ";
yyStringValue[j] = i;
countReference = i;
riSendString[j] = riString[i];
} else {
yyStringValue[j] = i;
sql += yyString[i].trim() + ",";
riSendString[j] = riString[i];
}
}
}
j++;
count++;
}
DBConn dbConn = new DBConn();
sql = sql.substring(0, sql.length() - 1);
if (countReference == 0) {
sql += " from rx_toiawase_kanri";
} else {
// 完了日SQL操作
sql += "," + yyString[35].trim()
+ " from rx_toiawase_kanri,thema_kanri";
}
System.out.println(sql);
ResultSet rsResultSet = null;
try {
rsResultSet = dbConn.executeQuery(sql);
} catch (Exception e2) {
e2.printStackTrace();
}
System.out.println((new SimpleDateFormat("yyyy-mm-dd hh-ss-mm"))
.format(new java.sql.Date(System.currentTimeMillis())));
int fieldsC = 0;
fieldsC = fieldsCc;
if (fieldsC == 0) {
System.out.println("kongshuju...");
return;
}
// /直接操作Excel
FileOutputStream fos = new FileOutputStream("D:/datacsv.csv");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "datacsv");
HSSFCellStyle setBorder = wb.createCellStyle();
String fileName = "datacsv.csv";
string = new StringBuffer("");
long m = 0;
HSSFRow row = null;
try {
while (rsResultSet.next()) {
m += 1;
for (int i = 0; i < j; i++) {
if (StringBlob[yyStringValue[i]].equals("Blob")) {
if (rsResultSet.getBlob(yyString[yyStringValue[i]]) != null) {
string.append(Textarea
.getContentString(
(rsResultSet
.getBlob(yyString[yyStringValue[i]])))
.substring(
4,
(Textarea
.getContentString((rsResultSet
.getBlob(yyString[yyStringValue[i]]))))
.length())
+ "//");
}
} else {
string.append(rsResultSet
.getObject(yyString[yyStringValue[i]]) + "//");
}
}
row = s.createRow((int) m - 1);
// selectString = selectString.substring(0,
// selectString.length() - 1);
String[] selectExcel = null;
selectExcel = selectString.split(",");// 字段名
System.out.println(selectString);
String stringExcel = string.toString();// 数据
stringExcel = stringExcel
.substring(0, stringExcel.length() - 2);
System.out.println(stringExcel);
String[] ExcelCell = stringExcel.split("//");
HSSFCell cell = null;
if (m == 1) {
for (int i = 0; i < selectExcel.length; i++) {
cell = row.createCell((short) i, 1);
HSSFRichTextString hts = new HSSFRichTextString(
selectExcel[i]);
cell.setCellValue(hts);
}
}
row = s.createRow((int) m);
for (int k = 0; k < ExcelCell.length; k++) {
cell = row.createCell((short) k, 1);
HSSFRichTextString hts = new HSSFRichTextString(
ExcelCell[k]);
cell.setCellValue(hts);
}
string.delete(0, string.length());
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// /
req.setAttribute("fileName", fileName);
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
try {
wb.write(fos);
fos.flush();
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("wanle,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,");
}
}
扫一扫
571
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
