/// <summary>
/// 默认生成模板样式
/// </summary>
public void FormatTamplate()
{
ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3");
rang.Select();
rang.Merge(false);
ws.Cells.set_Item(1, 1, "输出表");
ws.Cells.set_Item(2, 1, "表名称");
ws.Cells.set_Item(3, 1, "备注");
ws.Cells.set_Item(3, 2, this._tableName);
ws.Cells.set_Item(4, 1, "字段编号");
ws.Cells.set_Item(4, 2, "字段");
ws.Cells.set_Item(4, 3, "数据元素");
ws.Cells.set_Item(4, 4, "长度");
ws.Cells.set_Item(4, 5, "小数位");
ws.Cells.set_Item(4, 6, "类型");
ws.Cells.set_Item(4, 7, "字段含义");
ws.Columns.AutoFit();
}
/// <summary>
/// 默认生成模板样式
/// </summary>
public void FormatTamplate()
{
ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2");
rang.Select();
rang.Merge(false);
rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3");
rang.Select();
rang.Merge(false);
ws.Cells.set_Item(1, 1, "输出表");
ws.Cells.set_Item(2, 1, "表名称");
ws.Cells.set_Item(3, 1, "备注");
ws.Cells.set_Item(3,2, this._tableName);
ws.Cells.set_Item(4, 1, "字段编号");
ws.Cells.set_Item(4, 2, "字段");
ws.Cells.set_Item(4, 3, "数据元素");
ws.Cells.set_Item(4, 4, "长度");
ws.Cells.set_Item(4, 5, "小数位");
ws.Cells.set_Item(4, 6, "类型");
ws.Cells.set_Item(4, 7, "字段含义");
ws.Columns.AutoFit();
}
/// <summary>
/// Used to Insert the data that has been modified by the User
/// </summary>
/// <param name="dr">Gives the row Information</param>
/// <param name="dc">gives the column Information</param>
public static void convertRangeToXml()
{
ExcelTool.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[clsInformation.productRevenue]);
string[] columns = FAST._dsDownloadData.Tables[1].Rows[0].ItemArray[0].ToString().Split(',');
if (FAST._txtProcess == clsInformation.tcpuView)
{
FAST._lastColumnName = clsManageSheet.getColumnName(columns.Length);// removed the last column as it is Readonly and will be used only for TCPU
}
else
{
FAST._lastColumnName = clsManageSheet.getColumnName(columns.Length + 1);
}
Excel.Range header = worksheet.get_Range(clsManageSheet.formulaNextColumn + clsManageSheet.bodyRowStartingNumber, FAST._lastColumnName + clsManageSheet.bodyRowStartingNumber) as Excel.Range;
int rowCount = FAST._dsDownloadData.Tables[2].Rows.Count + clsManageSheet.bodyRowStartingNumber;
DataTable dt = FAST.makeTableFromRange(header, worksheet, rowCount);
List <string> table = new List <string>();
string[] months = new string[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
foreach (Excel.Range col in header)
{
if (FAST._txtProcess == clsInformation.accountingView)
{
string[] abc = Convert.ToString(col.Text).Split('/');
int a = Convert.ToInt16(abc[0]) - 1;
string val = months[a] + "' " + abc[2].Substring(abc[2].Length - 2);
table.Add(val);
}
else
{
//table.Add(Convert.ToString(col.Text).Replace("'", "-"));
table.Add(Convert.ToString(col.Text).Replace("'", @"\' "));
}
}
if (FAST._txtProcess == clsInformation.accountingView)
{
#region Xml
clearXmlRoot();
foreach (DataRow dr in dt.Rows)
{
foreach (var item in table)
{
_data.Element("XMLRoot").Add(new XElement("UpdatingRow",
new XElement("ProductLineId", dr["ProductLineId"]),
new XElement("ChannelId", dr["ChannelId"]),
new XElement("CountryId", dr["CountryId"]),
new XElement("ProgramId", dr["ProgramId"]),
new XElement("MemoryId", dr["MemoryId"]),
new XElement("WirelessId", dr["WirelessId"]),
new XElement("DTCPId", dr["DTCPId"]),
new XElement("CurrencyId", dr["CurrencyId"]),
new XElement("MonthYear", item),
new XElement("InputTypeValue", dr[item]),
new XElement("InputTemplateDataId", dr["InputTemplateDataId"])
));
}
}
#endregion
#region Commented CSV
//added by Praveen
//sb = new StringBuilder();
//string ddInputvalue = null;
//foreach (DataRow dr in dt.Rows)
//{
// foreach (var item in table)
// {
// #region Commented
// //_data.Element("XMLRoot").Add(new XElement("UpdatingRow",
// // new XElement("ProductLineId", dr["ProductLineId"]),
// // new XElement("ChannelId", dr["ChannelId"]),
// // new XElement("CountryId", dr["CountryId"]),
// // new XElement("ProgramId", dr["ProgramId"]),
// // new XElement("MemoryId", dr["MemoryId"]),
// // new XElement("WirelessId", dr["WirelessId"]),
// // new XElement("DTCPId", dr["DTCPId"]),
// // new XElement("CurrencyId", dr["CurrencyId"]),
// // new XElement("MonthYear", item),
// // new XElement("InputTypeValue", dr[item]),
// // new XElement("InputTemplateDataId", dr["InputTemplateDataId"])
// // ));
// #endregion
// ////Added by Praveen
// //if (dr[item].ToString() == "")
// // ddInputvalue = "null";
// //else
// // ddInputvalue = dr[item].ToString();
// //sb.AppendFormat("({0}, {1}, {2},{3},{4},{5},{6},{7},{8},{9},{10}),", dr["ProductLineId"], dr["ChannelId"], dr["CountryId"], dr["ProgramId"], dr["MemoryId"], dr["WirelessId"], dr["DTCPId"], dr["CurrencyId"], "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);
// }
//}
// sb = sb.Remove(sb.Length - 1, 1);
#endregion
}
else if (FAST._txtProcess == clsInformation.tcpuView)
{
sb = new StringBuilder();
string ddInputvalue = null;
sb.Clear();
foreach (DataRow dr in dt.Rows)
{
foreach (var item in table)
{
string itemValue = null;
if (item != "Life Time Value")
{
itemValue = Convert.ToString(item).Replace(" ", string.Empty).Replace("'", "-").Replace("\\", string.Empty);
}
else
{
itemValue = item;
}
if (dr[itemValue].ToString() == "")
{
ddInputvalue = "null";
}
else
{
ddInputvalue = dr[itemValue].ToString();
}
//Added by Sameera
//sb.AppendFormat("({0}, {1}, {2},{3}),", dr["CurrencyId"], "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);
sb.AppendFormat("({0}, {1}, {2}),", "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);
}
}
//Added by Sameera
sb = sb.Remove(sb.Length - 1, 1);
}
}