测试代码
private List<string> CurrentCheckedItems
{
get
{
return (List<string>)ViewState["CurrentCheckedItems"] ?? new List<string>();
}
set
{
ViewState["CurrentCheckedItems"] = value;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
EpochSoft.CostController.Inteface.VoucherCommonAdapter.YingChunExcelAdapter yinc = new EpochSoft.CostController.Inteface.VoucherCommonAdapter.YingChunExcelAdapter();
EpochSoft.CostController.Inteface.Fundation.AdapterRequestInfo info = new EpochSoft.CostController.Inteface.Fundation.AdapterRequestInfo();
this.CurrentCheckedItems = this.GetCheckedList();
info.RequestInfo = this.CurrentCheckedItems.ToArray();
EpochSoft.CostController.Inteface.Fundation.AdapterResponseInfo resInfo = yinc.Run(info);
}
private List<string> GetCheckedList()
{
List<string> result = new List<string>();
//string[] languages = { "WLZDH20110316009", "WLZDH20110317002" };
string[] languages = { "8b9687a2-3125-4211-a743-9b0334e6fdb5", "53a443c2-ed6a-43b0-b427-0cff708433a3", "6f4e990a-0f27-404b-83f8-60d667f1b22f" };
for(int i=0;i<languages.Length;i++){
result.Add(languages[i].ToString());
}
return result;
}
using System;
using System.Collections.Generic;
using System.Text;
using EpochSoft.CostController.Inteface.Fundation;
using System.Data;
using EpochSoft.CostController.Inteface.VoucherCommonAdapter.ESVoucherService;
using System.Data.OleDb;
namespace EpochSoft.CostController.Inteface.VoucherCommonAdapter
{
public class YingChunExcelAdapter : ICommonAdapter
{
#region ICommonAdapter Members
/// <summary>
/// 中烟凭证审核时的处理逻辑是：
/// a．在控制系统的“凭证审核”页面审核完凭证后点击“凭证审核”按钮，凭证自动通过接口将凭证传给总账系统（NC）。
/// b．NC系统接受到控制系统传递凭证后验证凭证的正确性，如果正确无误，反馈凭证号给控制系统，控制系统记录该凭证号。如果错误，则反馈错误信息，控制系统接收错误信息并显示给操作人员。
/// </summary>
/// <param name="input">input.RequestInfo数据包含一个voucherDataID</param>
/// <returns></returns>
public AdapterResponseInfo Run(AdapterRequestInfo input)
{
VoucherService vs = new VoucherService();
int state = vs.Logon("Epochsoft", "Epochsoft");
//vs.ExportVoucherModel(new string[] { "" });
AdapterResponseInfo responseInfo = new AdapterResponseInfo(true);
try
{
DataTable dt = new DataTable("BillMainDataTable");
VoucherItem[] items = vs.ExportVoucherModelByVoucherDataIDs(input.RequestInfo);
if (input.RequestInfo[0].ToString() != null)
{
string auxiliaryAccounStr = string.Empty;
for (int i = 0; i < items[0].Body[0].AuxiliaryAccountingItem.Length; i++)
{
auxiliaryAccounStr += items[0].Body[0].AuxiliaryAccountingItem[i].DimName + ",";
}
for (int j = 0; j < items[0].Body[0].Field.Length; j++)
{
if (auxiliaryAccounStr.IndexOf(items[0].Body[0].Field[j].FieldName) == -1)
{
dt.Columns.Add(new DataColumn(items[0].Body[0].Field[j].FieldName, typeof(string)));
}
}
dt.Columns.Add(new DataColumn("辅助核算", typeof(string)));
for(int ii=0;ii<items.Length;ii++)
{
string auxiliaryAccountStr = string.Empty;
string auxiliaryStr = string.Empty;
for (int i = 0; i < items[0].Body[0].AuxiliaryAccountingItem.Length; i++)
{
auxiliaryStr += items[0].Body[0].AuxiliaryAccountingItem[i].DimName + ",";
auxiliaryAccountStr += items[ii].Body[0].AuxiliaryAccountingItem[i].DimName + "---" + items[ii].Body[0].AuxiliaryAccountingItem[i].MemberCode + "---" + items[ii].Body[0].AuxiliaryAccountingItem[i].MemberName + "||";
}
auxiliaryAccountStr = auxiliaryAccountStr.Substring(0, auxiliaryAccountStr.Length - 2);
DataRow row = dt.NewRow();
for (int j = 0; j < items[0].Body[0].Field.Length; j++)
{
if (auxiliaryAccountStr.IndexOf(items[0].Body[0].Field[j].FieldName) == -1)
{
row[items[0].Body[0].Field[j].FieldName] = items[ii].Body[0].Field[j].Value.ToString();
}
}
row["辅助核算"] = auxiliaryAccountStr;
dt.Rows.Add(row);
}
}
BizExcel ImportExcel = new BizExcel();
ImportExcel.Write(dt);
}
catch (Exception ex)
{
responseInfo.IsSuccess = false;
}
return new AdapterResponseInfo(true);
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Web;
namespace EpochSoft.CostController.Inteface.VoucherCommonAdapter
{
///描述：此类用于通过OleDB对Excel进行操作
///创建者：Lixzh
///最后更改日期：2010.3.26
///
/// <summary>
/// 此枚举用来声明，Excel第一行是否作为表头
/// </summary>
public enum HDR
{
No = 0,//第一行不作为表头
Yes = 1//第一行作为表头
}
/// <summary>
/// 此枚举用来告诉驱动程序使用Excel文件的模式
/// </summary>
public enum IMEX
{
Export = 0,//导出
Import = 1,//导入
Mix = 2//混合
}
/// <summary>
/// 此类用于通过OleDB对Excel进行操作
/// </summary>
public class BizExcel
{
#region 字段
/// <summary>
/// 连接字符串
/// </summary>
private readonly string OleDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR={1};IMEX={2}\"";
private string filePath = string.Empty;
private string oleDbConnection = string.Empty;
private string hdr_yes = "Yes";
private string hdr_no = "No";
#endregion
#region 构造函数
public BizExcel() : this(DefaultFilePath) { }
public BizExcel(string filePath) : this(filePath, HDR.No) { }
public BizExcel(string filePath, HDR hdr) : this(filePath, hdr, IMEX.Export) { }
public BizExcel(HDR hdr, IMEX imex) : this(DefaultFilePath, hdr, imex) { }
public BizExcel(string filePath, HDR hdr, IMEX imex)
{
this.filePath = filePath;
this.oleDbConnection = string.Format(this.OleDBConnection, filePath, this.GetHDRString(hdr), this.GetIMEXString(imex));
}
#endregion
#region 公有方法
public DataSet Read()
{
return this.Read(false);
}
public DataSet Read(bool deleteTmpFile)
{
this.EnsurePath();
DataSet ds = new DataSet();
using (OleDbConnection conn = this.CurrentConnection)
{
conn.Open();
this.CreateDataSet(ds, conn);
}
if (deleteTmpFile)
{
this.Dispose();
}
return ds;
}
public void Write(DataSet ds)
{
foreach (DataTable dt in ds.Tables)
{
this.Write(dt);
}
}
public void Write(DataTable dt)
{
using (OleDbConnection conn = this.CurrentConnection)
{
conn.Open();
string createSql = this.GetCreateSql(dt);
this.ExecuteNonQuery(conn, createSql);
List<string> columns = this.GetColumns(dt);
foreach (DataRow dr in dt.Rows)
{
string insertSql = this.GetInsertSql(dt, columns, dr);
this.ExecuteNonQuery(conn, insertSql);
}
}
}
public void Dispose()
{
File.Delete(this.FilePath);
}
#endregion
#region 私有方法
private string GetInsertSql(DataTable dt, List<string> columns, DataRow dr)
{
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("INSERT INTO [{0}] VALUES(", dt.TableName));
foreach (string columnName in columns)
{
object o = dr[columnName];
sb.Append(string.Format("\t'{0}',", o == null ? string.Empty : o.ToString()));
}
string insertSql = sb.ToString();
insertSql = (insertSql.EndsWith(",") ? insertSql.Substring(0, insertSql.LastIndexOf(",")) : insertSql) + ")";
return insertSql;
}
private void ExecuteNonQuery(OleDbConnection conn, string createSql)
{
OleDbCommand command = this.GetCommand(conn, createSql);
command.ExecuteNonQuery();
}
private string GetCreateSql(DataTable dt)
{
string dtName = dt.TableName;
List<string> columns = this.GetColumns(dt);
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("CREATE TABLE [{0}] (", dtName));
foreach (string columnName in columns)
{
sb.Append(string.Format(" [{0}] string,", columnName));
}
string createSql = sb.ToString();
createSql = (createSql.EndsWith(",") ? createSql.Substring(0, createSql.LastIndexOf(",")) : createSql) + ")";
return createSql;
}
/// <summary>
/// 创建数据集，把Excel文件读取到数据集中，并且每个Sheet都是一张表
/// </summary>
/// <param name="ds">数据集对象</param>
/// <param name="conn">连接对象</param>
private void CreateDataSet(DataSet ds, OleDbConnection conn)
{
List<string> sheets = this.GetExcelTables(conn);
foreach (string sheetName in sheets)
{
this.ReadSheetToTable(conn, sheetName, ds);
}
}
/// <summary>
/// 把Excel的Sheet读到DataTable中
/// </summary>
/// <param name="conn">连接对象</param>
/// <param name="sheetName">SheetName</param>
/// <returns>DataTable</returns>
private void ReadSheetToTable(OleDbConnection conn, string sheetName, DataSet ds)
{
OleDbCommand command = this.GetCommand(conn, string.Format(" SELECT * FROM [{0}$] ", sheetName));
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
ds.Tables.Add(new DataTable(sheetName));
adapter.Fill(ds, sheetName);
}
private OleDbCommand GetCommand(OleDbConnection conn, string sqlString)
{
OleDbCommand command = new OleDbCommand(sqlString, conn);
return command;
}
/// <summary>
/// 验证路径是否合法
/// </summary>
private void ValidatePath()
{
if (!File.Exists(this.filePath))
{
throw new FileNotFoundException();
}
}
/// <summary>
/// 验证路径是否合法
/// </summary>
private void EnsurePath()
{
this.ValidatePath();
}
private string GetHDRString()
{
return this.GetHDRString(HDR.No);
}
private string GetHDRString(HDR hdr)
{
return hdr == HDR.Yes ? hdr_yes : hdr_no;
}
private string GetIMEXString(IMEX imex)
{
return ((int)imex).ToString();
}
/// <summary>
/// 获取指定表名的所有列
/// </summary>
/// <param name="oConn">连接对象</param>
/// <param name="tableName">表名</param>
/// <returns>列名列表</returns>
private List<string> GetColumns(OleDbConnection oConn, string tableName)
{
DataTable columnTable = this.GetColumnTable(oConn, tableName);
List<string> colList = new List<string>();
foreach (DataRow dr in columnTable.Rows)
{
colList.Add(dr["Column_Name"].ToString());
}
return colList;
}
private List<string> GetColumns(DataTable dt)
{
List<string> result = new List<string>();
foreach (DataColumn dc in dt.Columns)
{
result.Add(dc.ColumnName);
}
return result;
}
private DataTable GetColumnTable(OleDbConnection oConn, string tableName)
{
DataTable columnTable = oConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
return columnTable;
}
/// <summary>
/// 获取指定Excel的所有列名
/// </summary>
/// <param name="oConn">连接对象</param>
/// <returns>表名列表</returns>
private List<string> GetExcelTables(OleDbConnection oConn)
{
DataTable t = this.GetTables(oConn);
List<string> tableNames = new List<string>();
foreach (DataRow dr in t.Rows)
{
string tableName = dr["Table_Name"].ToString();
string _name = string.Empty;
if (tableName.EndsWith("$"))
{
_name = dr["Table_Name"].ToString();
_name = _name.Substring(0, _name.Length - 1);
if (!tableNames.Contains(_name))
{
tableNames.Add(_name);
}
}
}
return tableNames;
}
private DataTable GetTables(OleDbConnection oConn)
{
DataTable t = oConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
return t;
}
#endregion
#region 属性
private OleDbConnection CurrentConnection
{
get
{
return new OleDbConnection(this.oleDbConnection);
}
}
public string FilePath
{
get
{
return this.filePath;
}
}
private static string DefaultFilePath
{
get
{
Guid newid = new Guid();
// return newid+".xls";// HttpContext.Current.Server.MapPath(BizCommon.TemplateFolder) + SysShared.GetNewGuid() + ".xls";
return HttpContext.Current.Server.MapPath("TemplateFolder") + System.Guid.NewGuid() + ".xls";
}
}
#endregion
}
}