解析excel+结合表单上的数据生成csv文件
来源:互联网 发布:淘宝店铺封店重开技巧 编辑:程序博客网 时间:2024/05/29 15:38
个人网站:友书小说网(http://laiyoushu.com)绿色纯净无广告,欢迎大家前来看小说
using System;
using System.Collections.Generic;using System.Linq;
using System.Web;
using System.Web.SessionState;
using Sjune.Common;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data.SqlClient;
using NPOI.HSSF.Util;
using NPOI.HPSF;
using System.Text;
using Sjune.Model;
using System.Reflection;
namespace Sjune.Web.program.ashx
{
/// <summary>
/// Upload_order 的摘要说明
/// </summary>
public class Upload_order : Web.UI.UserPage, IHttpHandler, IRequiresSessionState
{
protected HSSFWorkbook hssfworkbook;
public static string path = HttpContext.Current.Request.PhysicalApplicationPath;
public void ProcessRequest(HttpContext context)
{
try
{
context.Response.ContentType = "text/json";
context.Response.Buffer = true;
context.Response.ExpiresAbsolute = DateTime.Now.AddDays(-1);
context.Response.AddHeader("pragma", "no-cache");
context.Response.AddHeader("cache-control", "");
context.Response.CacheControl = "no-cache";
var logName = Session["loginName"];
string action = context.Request["action"] + "";
Model.MV_Upload_order db_model = new Model.MV_Upload_order();
List<ModelExcel> list = new List<ModelExcel>();
//开始解析之前上传excel中的数据
try
{
StringBuilder sbr = new StringBuilder();
var PreviousUrl = context.Request["url"].ToString();
PreviousUrl = path + PreviousUrl;
using (FileStream fs = File.OpenRead(PreviousUrl.ToString())) //打开myxls.xls文件
{
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
{
ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
if (j > 0)
{
if (row != null)
{
ModelExcel lu = new ModelExcel();
lu.Sign_Code = row.GetCell(0).ToString();
//lu.Special_Code = row.GetCell(1).ToString();
lu.Counts = (row.GetCell(1).ToString()).ToInt();
lu.Unit = row.GetCell(2).ToString() == "个" ? "E" : "C";
list.Add(lu);
}
}
}
}
}
}
catch (Exception ex)
{
context.Response.Write(ex.ToString());
}
string timeDay = DateTime.Now.ToString("yyyyMMdd");
db_model.User_Account = context.Request["User_Account"].ToString();
db_model.Pro_Name = context.Request["Pro_Name"].ToString();
db_model.Buyer_Name = context.Request["Buyer_Name"].ToString();
db_model.Company = context.Request["Company"].ToString();
db_model.Buyer_Tel = context.Request["Buyer_Tel"].ToString();
db_model.Buyer_Email = context.Request["Buyer_Email"].ToString();
db_model.Upload_Cause = context.Request["Upload_Cause"].ToString();
db_model.PO = context.Request["PO"].ToString();
db_model.Start_Date = context.Request["Start_Date"].ToDate().ToString("yyyy-MM-dd");
db_model.Order_Status = "1";
db_model.Order_Date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
db_model.U_Other_Account = context.Request["U_Other_Account"].ToString();
db_model.is_del = 0;
//这里生成6位数的流水号
db_model.SerialNumber = OrderNoHelper.OrderID;
//生成流水号
db_model.Web_Ord_Number = timeDay + db_model.Upload_Cause + db_model.SerialNumber.ToString();
string[] TBBody = { db_model.Web_Ord_Number, db_model.User_Account, db_model.U_Other_Account, db_model.Order_Date.Substring(0, 10), db_model.PO.ToString(), db_model.Upload_Cause, db_model.Start_Date };
string sNewFileName = Utils.GetRamCode();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("{\"Status\":\"");
sb.Append("/UploadSys/" + db_model.Web_Ord_Number + ".xls");
sb.Append("\"}");
var url = sb.ToString();
url = url.Substring(11);
url = path + url.Substring(0, url.LastIndexOf("xls")) + "csv"; ;
List<ModelCSV> csvList = new List<ModelCSV>();
for (int j = 0; j < list.Count; j++)
{
ModelCSV mcs = new ModelCSV();
mcs.Sign_Code = "#" + list[j].Sign_Code;
mcs.Counts = "#" + list[j].Counts.ToString();
mcs.Unit = list[j].Unit;
mcs.Web_Ord_Number = "#" + TBBody[0];
mcs.User_Account = "#" + TBBody[1];
mcs.U_Other_Account = "#" + TBBody[2];
mcs.Order_Date = TBBody[3];
mcs.PO = "#" + TBBody[4];
mcs.Upload_Cause = "#" + TBBody[5];
mcs.SSD = TBBody[6];
csvList.Add(mcs);
}
SaveAsCSV(url, csvList);
var UserUrl = context.Request["url"].ToString();
db_model.File_User_Number = UserUrl.Substring(UserUrl.LastIndexOf("/") + 1);
db_model.File_Sys_Number = url.Substring(url.LastIndexOf("/") + 1); ;
if (new BLL.Upload_Order().Add(db_model) > 0)
{
context.Response.Write(JsonHelper.ObjectToJSON(new { Status = 0, Message = "数据添加成功", Data = "" }));
}
else
{
context.Response.Write(JsonHelper.ObjectToJSON(new { Status = 1, Message = "数据添加失败,请刷新页面重试", Data = "" }));
}
}
catch (Exception)
{
throw;
}
}
protected void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}
public bool IsReusable
{
get
{
return false;
}
}
//生成csv文件
public static bool SaveAsCSV<T>(string fileName, IList<T> listModel) where T : class, new()
{
bool flag = false;
try
{
StringBuilder sb = new StringBuilder();
//通过反射 显示要显示的列
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识
Type objType = typeof(T);
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
string header = string.Empty;
List<string> listPropertys = new List<string>();
foreach (PropertyInfo info in propInfoArr){
if (string.Compare(info.Name.ToUpper(), "ID") != 0) //不考虑自增长的id或者自动生成的guid等
{
if (!listPropertys.Contains(info.Name))
{
listPropertys.Add(info.Name);
}
header += info.Name + ",";
}
}
header = "Web Order Number" + "," + "Customer Account Number" + "," + "Customer Branch Account" + "," + "Order date" + "," + "PO Number" + "," + "Order Type" + "," + "SS Date" + "," + "Product ID(Product Identification Code)" + "," + "QTY(Quantity)" + "," + "Unit";
sb.AppendLine(header.Trim(',')); //csv头
{
string strModel = string.Empty;
foreach (string strProp in listPropertys)
{
foreach (PropertyInfo propInfo in propInfoArr)
{
if (string.Compare(propInfo.Name.ToUpper(), strProp.ToUpper()) == 0)
{
PropertyInfo modelProperty = model.GetType().GetProperty(propInfo.Name);
if (modelProperty != null)
{
object objResult = modelProperty.GetValue(model, null);
string result = ((objResult == null) ? string.Empty : objResult).ToString().Trim();
if (result.IndexOf(',') != -1)
{
result = "\"" + result.Replace("\"", "\"\"") + "\""; //特殊字符处理
//result = result.Replace("\"", "“").Replace(',', ',') + "\"";
}
if (!string.IsNullOrEmpty(result))
{
Type valueType = modelProperty.PropertyType;
if (valueType.Equals(typeof(Nullable<decimal>)))
{
result = decimal.Parse(result).ToString("#.#");
}
else if (valueType.Equals(typeof(decimal)))
{
result = decimal.Parse(result).ToString("#.#");
}
else if (valueType.Equals(typeof(Nullable<double>)))
{
result = double.Parse(result).ToString("#.#");
}
else if (valueType.Equals(typeof(double)))
{
result = double.Parse(result).ToString("#.#");
}
else if (valueType.Equals(typeof(Nullable<float>)))
{
result = float.Parse(result).ToString("#.#");
}
else if (valueType.Equals(typeof(float)))
{
result = float.Parse(result).ToString("#.#");
}
}
strModel += result + ",";
}
else
{
strModel += ",";
}
break;
}
}
}
strModel = strModel.Substring(0, strModel.Length - 1);
sb.AppendLine(strModel);
}
string content = sb.ToString();
string dir = Directory.GetCurrentDirectory();
string fullName = Path.Combine(dir, fileName);
if (File.Exists(fullName)) File.Delete(fullName);
using (FileStream fs = new FileStream(fullName, FileMode.CreateNew, FileAccess.Write))
{
StreamWriter sw = new StreamWriter(fs, Encoding.Default);
sw.Flush();
sw.Write(content);
sw.Flush();
sw.Close();
}
flag = true;
}
catch
{
flag = false;
}
return flag;
}
//csvmodelpublic class ModelCSV
{
public string Web_Ord_Number { get; set; }
public string User_Account { get; set; }
public string U_Other_Account { get; set; }
public string Order_Date { get; set; }
public string PO { get; set; }
public string Upload_Cause { get; set; }
public string SSD { get; set; }
public string Sign_Code { get; set; }
public string Counts { get; set; }
public string Unit { get; set; }
}
}
}
1 0
- 解析excel+结合表单上的数据生成csv文件
- Struts结合数据实时生成Excel文件并直接下载
- CSV文件的生成
- List数据生成CSV文件
- cocos2d-x CSV文件读取 (Excel生成csv文件)
- cocos2d-x CSV文件读取 (Excel生成csv文件)
- DataGridView生成CSV,XML 和 EXCEL文件
- DataGridView生成CSV,XML 和 EXCEL文件
- PHP 动态导出生成 excel,csv文件
- JavaScript将页面表格数据导出为Excel、CSV格式文件(结合JQuery EasyUI的grid )
- php 生成csv文件与解析csv文件
- 导出(下载)数据库查询的数据生成csv文件
- Ubuntu 解析以逗号“,”分割数据的csv文件
- 读取Excel和CSV文件数据
- 使用CSV文件处理EXcel数据
- xlsx文件解析处理:openpyxl库 csv文件格式生成:csv
- iOS-解析读取CSV文件,解析excel文件
- python解析csv文件 提取数据
- 抽象工厂模式
- git删除远程文件夹或文件的方法
- js操作符优先级
- Kafka从入门到实践
- 使用Angular4.0开发星级评价组件
- 解析excel+结合表单上的数据生成csv文件
- 百度地图定位
- Android studio打包所遇到的问题
- MongoDB-Java链接超时问题解决汇总
- 正则表达式规则
- mongodb高级教程
- TraceView 使用
- 计算机视觉业界牛人
- 1.4 URL管理器