That actually relates to transferring code you wrote, and for such process like data integration, transporation which may involve some uncertain factors like network issue, file's integrity issue and etc, we do not suggest to achieve that in ASP.Net app,
casue ASP.NET cannot provide a robust way to monitor,control, trace the whole transportation procedure. Try to use SSIS(data import&export wizard) and give your customers corresponding previlige on the objects they need.
I Tried SSIS for generate excel file its working fine but i want formated excel file that means some of the column i want to meke it bold some of the rows i want to make it group so i want to draw a rectangle box for that particular rows how to do this kind
of design work in SSIS pls help me to move forward thanks.
kumarpgp
Member
282 Points
118 Posts
Datatable to Excel workbook
May 18, 2010 03:48 AM|LINK
Hi All,
I want to gentrate a Excel workbook contains serveral Worksheet.
I have 5 Datatable i want to transfer this information to excel each worksheet contains one datatable information.
I already completed this code but it's working fine in local, when i publish this application in server it's not working because of Printer setting.
once i change the default printer setting then it's working and also its taking more time for generate the excel.
if i didn't change the default printer name i got the error message is "Unable to set the LeftHeader property of the PageSetup class"
My question is why it's take more time in server and how to set the default printer using C# code can any one pls help me.
Thanks & Regards
Kumar...
Nai-Dong Jin...
All-Star
41630 Points
3558 Posts
Re: Datatable to Excel workbook
May 19, 2010 05:43 AM|LINK
Hi,
For the first question, I think you may try the following thread,
http://forums.asp.net/t/1169508.aspx
>>it's take more time in server
That actually relates to transferring code you wrote, and for such process like data integration, transporation which may involve some uncertain factors like network issue, file's integrity issue and etc, we do not suggest to achieve that in ASP.Net app, casue ASP.NET cannot provide a robust way to monitor,control, trace the whole transportation procedure. Try to use SSIS(data import&export wizard) and give your customers corresponding previlige on the objects they need.
Thanks.
kumarpgp
Member
282 Points
118 Posts
Re: Datatable to Excel workbook
May 26, 2010 04:40 AM|LINK
Hi Nai-Dong Jin,
I Tried SSIS for generate excel file its working fine but i want formated excel file that means some of the column i want to meke it bold some of the rows i want to make it group so i want to draw a rectangle box for that particular rows how to do this kind of design work in SSIS pls help me to move forward thanks.
Thanks
Kumar...
kumarpgp
Member
282 Points
118 Posts
Re: Datatable to Excel workbook
Nov 07, 2012 11:02 PM|LINK
//##################################################################################
private void TIMMakeTableInSheets(string[] names, string[] tables, string CustAbbr,
string CustGroup, string CopyMark)
{
object oMissing = System.Reflection.Missing.Value;
Application myExcel = new Microsoft.Office.Interop.Excel.Application();
_Workbook myworkbook = myExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet oSheet;
Sheets mysheets = myworkbook.Worksheets;
string topheader = string.Empty;
DateTime issusedates = DateTime.Parse(Commonmethod.getID("EffectiveDate",
"SA_Quote", " QuoteRefNo='" + DDL_Qutationref.SelectedItem.Text + "'"));
string dates = issusedates.ToString("yyyy-MMM-dd");
try
{
int Headernumber = names.Length;
string Customer = string.Empty;
if (DDL_CustAbbr.SelectedItem.Text != "Select")
{
Customer = DDL_CustAbbr.SelectedItem.Text;
}
else { Customer = DDL_CustGroup.SelectedItem.Text; }
if (File.Exists(@"C:\\inetpub\\wwwroot\\SalesAdmin\\FileChecking\\" +
DDL_Qutationref.SelectedItem.Text + ".xls"))
{
System.IO.File.Delete(@"C:\inetpub\wwwroot\SalesAdmin\FileChecking\" +
DDL_Qutationref.SelectedItem.Text + ".xls");
}
for (int i = 1; i <= names.Length; i++)
{
#region "Process"
topheader = string.Empty;
if (File.Exists(@"C:\\inetpub\\wwwroot\\SalesAdmin\\FileChecking\\" +
DDL_Qutationref.SelectedItem.Text + ".xls"))
{
myworkbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
oSheet = (Worksheet)mysheets.get_Item(1);
oSheet.Name = names[i - 1].ToString().Replace("SETUP", "");
Microsoft.Office.Interop.Excel.Range objcellx = oSheet.Cells;
if ((names[i - 1].ToUpper().ToString()) == "OTHER CHARGES")
{
Dt = CustQoutREf.CallQuotation(Customer, DDL_Qutationref.SelectedItem.Text,
CopyMark, "OTHERCHARGES", "EnggLot",
CustAbbr, CustGroup);
System.Data.DataTable DTNF = CustQoutREf.CallQuotation(Customer,
DDL_Qutationref.SelectedItem.Text, CopyMark,
"OTHERCHARGES", "NonFunctional", CustAbbr, CustGroup);
System.Data.DataTable DTNFRW = CustQoutREf.CallQuotation(Customer,
DDL_Qutationref.SelectedItem.Text, CopyMark,
"OTHERCHARGES", "NonFunctionalRework", CustAbbr, CustGroup);
System.Data.DataTable DTNS = CustQoutREf.CallQuotation(Customer,
DDL_Qutationref.SelectedItem.Text, CopyMark,
"OTHERCHARGES", "NonSalesItem", CustAbbr, CustGroup);
OtherWriteData(Dt, DTNF, DTNFRW, DTNS, oSheet, topheader, objcellx,
names[i - 1].ToString(), 16, 20, 100);
}
else if ("TERMS & CONDITIONS" == names[i - 1].ToString())
{
ReadText(objcellx, oSheet, @"C:\inetpub\wwwroot\SalesAdmin\FileChecking\" +
DDL_Revision.SelectedItem.Value + ".txt");
}
else if ("COVER PAGE" == names[i - 1].ToString())
{
#region "CoverPage"
string PaymentTerm = string.Empty;
if (DDL_CustAbbr.SelectedItem.Text != "Select")
{
System.Data.DataTable Dtm = Commonmethod.data("Select PayTerm from " +
"SA_Custmaster where CustAbbr='" +
DDL_CustAbbr.SelectedItem.Text + "'");
if (Dtm.Rows.Count > 0)
{
Dtm = Commonmethod.data("Select Description from SA_Payterm where " +
"PaymentCode='" + Dtm.Rows[0][0].ToString() + "'");
PaymentTerm = Dtm.Rows[0][0].ToString();
}
}
else if (DDL_CustGroup.SelectedItem.Text != "Select")
{
System.Data.DataTable Dtm = Commonmethod.data("Select CustAbbr," +
"PayTerm from SA_Custmaster where CustGroup='" +
DDL_CustGroup.SelectedItem.Text + "'");
if (Dt.Rows.Count > 0)
{
for (int ii = 0; ii < Dtm.Rows.Count; ii++)
{
System.Data.DataTable Dts = Commonmethod.data("Select " +
"Description from SA_Payterm where PaymentCode='" +
Dtm.Rows[0][1].ToString() + "'");
if (Dts.Rows.Count > 0)
{
if (Dts.Rows[0][0].ToString() != string.Empty)
{
PaymentTerm = Dts.Rows[0][0].ToString();
break;
}
}
}
}
}
TIMCoverpage(objcellx, oSheet, names, PaymentTerm);
Array.Reverse(names);
#endregion
}
else if ("QUOTE CHANGES" == names[i - 1].ToString())
{
#region "FirstPageNotes"
Dt = new System.Data.DataTable();
Dt.Columns.Add("1"); /**/ Dt.Columns.Add("2");
System.Data.DataTable Dts = Commonmethod.data("select items,Header " +
",Description from SA_Quotechange where QuoteRefNo='" +
DDL_Qutationref.SelectedItem.Text + "'");
for (int M = 0; M < Dts.Rows.Count; M++)
{
DataRow dr = Dt.NewRow(); /**/
dr["1"] = Dts.Rows[M]["items"].ToString().ToUpper();
dr["2"] = Dts.Rows[M]["Header"].ToString().ToUpper();
Dt.Rows.Add(dr); /**/ dr = Dt.NewRow();
dr["1"] = string.Empty;
dr["2"] = Dts.Rows[M]["Description"].ToString().ToUpper();
Dt.Rows.Add(dr);
}
System.Data.DataTable newdt = new System.Data.DataTable();
OtherWriteData(Dt, newdt, newdt, newdt, oSheet,
"ITEMIZED CHANGES T0 QUOTATION " + DDL_Qutationref.SelectedItem.Text, objcellx,
"QUOTE CHANGES", 16, 20, 100);
//WriteData(Dt, oSheet, "ITEMIZED CHANGES T0 QUOTATION " +
// DDL_Qutationref.SelectedItem.Text, objcellx, "QUOTE CHANGES");
oSheet.Protect((object)"password", true, true, true, true, false, false,
false, false, false, false, false, false, false, false, false);
myworkbook.CheckCompatibility = false;
myworkbook.Save();
#endregion
}
else if ("DEVICE PRICING" == names[i - 1].ToString())
{
string Custvalue = string.Empty;
if (CustAbbr == string.Empty)
{
string query = "select CustAbbr,CustGroup from SA_CustMaster where CustGroup ='" +
CustGroup + "'";
System.Data.DataTable DTCustAbb = Commonmethod.data(query);
Custvalue = string.Join("','", (from DataRow row in DTCustAbb.Rows
select (string)row["CustAbbr"]).ToArray());
}
Dt = new System.Data.DataTable();
Dt = PriceMatrix(DDL_Qutationref.SelectedItem.Text, Custvalue);
WritePriceData(oSheet, topheader, objcellx, names[i - 1].ToString(),
Custvalue, Dt);
}
else
{
Dt = CustQoutREf.CallQuotation(Customer, DDL_Qutationref.SelectedItem.Text,
CopyMark, names[i - 1].ToString(), string.Empty,
CustAbbr, CustGroup);
WriteData(Dt, oSheet, topheader, objcellx, names[i - 1].ToString());
}
#region "Header & Footer"
if (names[i - 1].ToString() != "COVER PAGE")
{
string[] CustName = this.CustomerDetail();
string Header = string.Empty;
if (names[i - 1].ToString() == "GOLD ADDER")
{
Header = "Gold Price Adder Summary Tables";
}
else if (names[i - 1].ToString() == "OTHER CHARGES")
{
Header = "OTHER CHARGES";
}
else { Header = names[i - 1].ToString(); }
oSheet.PageSetup.LeftHeader = "&9 &\"Calibri,Bold\" Carsem Quotation " +
Environment.NewLine + " &\"Calibri\" Effective Date:" + dates;
oSheet.PageSetup.CenterHeader = "&9 &\"Calibri,Bold\"" + CustName[0].ToString();
oSheet.PageSetup.RightHeader = "&9 &\"Calibri\" Ref: " +
DDL_Qutationref.SelectedItem.Text.Trim().ToUpper() +
Environment.NewLine + "Section" + (Headernumber - 1) + ":" + Header;
Headernumber = Headernumber - 1;
}
oSheet.Protect((object)"password", true, true, true, true, false, false,
false, false, false, false, false, false, false, false, false);
#endregion
myworkbook.CheckCompatibility = false;
myworkbook.Save();
}
else
{
#region "Term&Condition"
myworkbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
oSheet = (_Worksheet)mysheets.get_Item(i);
oSheet.Name = names[i - 1].ToString().Replace("SETUP", "");
Microsoft.Office.Interop.Excel.Range objcellx = oSheet.Cells;
ReadText(objcellx, oSheet, @"C:\inetpub\wwwroot\SalesAdmin\FileChecking\" +
DDL_Revision.SelectedItem.Value + ".txt");
oSheet.Protect((object)"password", true, true, true, true, false, false,
false, false, false, false, false, false, false, false, false);
myworkbook.CheckCompatibility = false;
myExcel.DisplayAlerts = false;
myworkbook.SaveAs(@"C:\inetpub\wwwroot\SalesAdmin\FileChecking\" +
DDL_Qutationref.SelectedItem.Text + ".xls".Trim(),
XlFileFormat.xlWorkbookNormal, oMissing, oMissing, oMissing, oMissing,
XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing,
oMissing);
#endregion
}
#endregion
}
myworkbook.CheckCompatibility = false;
myworkbook.Save();
myworkbook.Close(Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
myworkbook.CheckCompatibility = false;
myworkbook.Save();
myworkbook.Close(Type.Missing, Type.Missing, Type.Missing);
lblmsg = ex.ToString().Replace("'", string.Empty);
}
}
//-----------------------------------------------------------------------------------------------------------------------
//###################################################################################
public void WriteContent(Microsoft.Office.Interop.Excel.Range rg, string startvalue,
_Worksheet oSheet, string Content, int size, string bold, string position,
int columnwith, int row, string line)
{
rg = oSheet.get_Range(startvalue, Type.Missing);
rg.Font.Size = size;
if (bold == "true") { rg.Font.Bold = true; }
else { rg.Font.Bold = false; }
rg.WrapText = true;
if (position == "Center") { rg.HorizontalAlignment = Constants.xlCenter; }
else if (position == "Right") { rg.HorizontalAlignment = Constants.xlRight; }
else { rg.HorizontalAlignment = Constants.xlLeft; }
rg.VerticalAlignment = Constants.xlTop;
rg.Interior.ColorIndex = 2; /**/ rg.Borders.Weight = 2;
string[] valuesd = line.Split('#');
if (valuesd.Length > 1)
{
lines = valuesd[0].Split(',');
removeline = valuesd[1].Split(',');
}
else
{
lines = valuesd[0].Split(',');
string val = string.Empty;
removeline = val.Split(',');
}
for (int i = 0; i < lines.Length; i++)
{
if (lines[i].ToString() == "Top")
{
rg.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
}
else if (lines[i].ToString() == "Bottom")
{
rg.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}
else if (lines[i].ToString() == "Right")
{
rg.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
}
else if (lines[i].ToString() == "Left")
{
rg.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
}
else if (lines[i].ToString() == "None")
{ rg.Borders.LineStyle = Constants.xlNone; }
else { rg.Borders.LineStyle = XlLineStyle.xlContinuous; }
}
for (int i = 0; i < removeline.Length; i++)
{
if (removeline[i].ToString() == "Top")
{
rg.Borders[XlBordersIndex.xlEdgeTop].LineStyle = Constants.xlNone;
}
else if (removeline[i].ToString() == "Bottom")
{
rg.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Constants.xlNone;
}
else if (removeline[i].ToString() == "Right")
{
rg.Borders[XlBordersIndex.xlEdgeRight].LineStyle = Constants.xlNone;
}
else if (removeline[i].ToString() == "Left")
{
rg.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = Constants.xlNone;
}
}
rg.Value2 = Content;
if (Content.Length > 30)
{
int a = Content.Length / 100;
a = a * 16;
rg.Cells.RowHeight = a;
}
else { rg.Cells.RowHeight = 16; }
rg.Cells.ColumnWidth = columnwith; /**/ oSheet.Cells.EntireColumn.AutoFit();
oSheet.Cells.EntireRow.AutoFit();
}
//###################################################################################
public void WriteData(System.Data.DataTable DTs, _Worksheet oSheet, string topheader,
Microsoft.Office.Interop.Excel.Range objcells, string header)
{
int iRow, iCol, cel1; /**/ objcells.ColumnWidth = 2;
objcells = oSheet.get_Range(oSheet.Cells[100, 100], oSheet.Cells[1, 1]);
objcells.Interior.ColorIndex = 2; /**/ cel1 = 0;
string sCustcode = string.Empty; /**/ string sCustgroup = string.Empty;
string sGoldbase = string.Empty;
System.Data.DataTable DTquotes = Commonmethod.data("select CustCode,CustGroup " +
"from SA_Quote where QuoteRefNo='" + DDL_Qutationref.SelectedItem.Text + "'");
if (DTquotes.Rows.Count > 0)
{
sCustcode = DTquotes.Rows[0]["Custcode"].ToString();
sCustgroup = DTquotes.Rows[0]["Custgroup"].ToString();
DTquotes = Commonmethod.data("select Goldbase from SA_WRFCT " +
"where CustCode='" + sCustcode + "' and CustGroup='" + sCustgroup + "' and " +
"EndDate >= CURRENT_TIMESTAMP ");
if (DTquotes.Rows.Count > 0)
{
sGoldbase = DTquotes.Rows[0]["Goldbase"].ToString();
}
else
{
DTquotes = Commonmethod.data("select Goldbase from SA_WRFCT " +
"where CustCode='' and CustGroup='' and EndDate >= CURRENT_TIMESTAMP ");
if (DTquotes.Rows.Count > 0)
{
sGoldbase = DTquotes.Rows[0]["Goldbase"].ToString();
}
}
}
for (iCol = 0; iCol < DTs.Columns.Count; iCol++)
{
objcells.Font.Size = 16; /**/
if (iCol == 0) { objcells.ColumnWidth = 2; }
else if (DTs.Columns[iCol].ToString().Contains("Comment"))
{ objcells.ColumnWidth = 18; }
else if (DTs.Columns[iCol].ToString().Contains("Price") ||
DTs.Columns[iCol].ToString().Contains("Lead"))
{
objcells.ColumnWidth = 7;
}
else { objcells.ColumnWidth = 15; }
objcells.WrapText = true;
string columnsvalue = string.Empty;
if (DTs.Columns[iCol].ToString().Contains("MilAU"))
{
columnsvalue = "BASE UNIT PRICE US$ (AU";
if (sGoldbase != string.Empty)
{
columnsvalue += "$ " + sGoldbase + "/oz) ";
}
columnsvalue += DTs.Columns[iCol].ToString().Remove(
DTs.Columns[iCol].ToString().Length - 5) + " AU";
}
else if (DTs.Columns[iCol].ToString().Contains("MilCU"))
{
columnsvalue = "BASE UNIT PRICE US$ (CU";
if (sGoldbase != string.Empty)
{
columnsvalue += "$ " + sGoldbase + "/oz) ";
}
columnsvalue += DTs.Columns[iCol].ToString().Remove(
DTs.Columns[iCol].ToString().Length - 5) + " CU";
}
else { columnsvalue = DTs.Columns[iCol].ToString(); }
if (columnsvalue == "1" || columnsvalue == "2")
{
oSheet.Cells[3, iCol + 1] = " ";
}
else
{
oSheet.Cells[3, iCol + 1] = columnsvalue.ToUpper();
cel1 = cel1 + 1;
string cell1 = numchar((cel1).ToString());
objcells = oSheet.get_Range(cell1 + "3", cell1 + "3");
objcells.Interior.Color = System.Drawing.Color.LightGray.ToArgb();
objcells.Borders.Color = System.Drawing.Color.Black.ToArgb();
objcells.Font.Color = System.Drawing.Color.Black.ToArgb();
}
}
string query = "select HeaderText,FooterText from SA_QuotationHeaderInfo " +
"where TypeOfProcess='" + header + "' and CustAbbr='" + CustCode + "'";
System.Data.DataTable dtm = new System.Data.DataTable();
dtm = Commonmethod.data(query);
DataRow[] drd = dtm.Select("HeaderText<>''");
int value = 0;
if (drd.Length > 0)
{
value = 4 + value;
cel1 = 1;
int cell2 = DTs.Columns.Count;
for (int m = 0; m < drd.Length; m++)
{
value = value + m;
if (drd[m][0].ToString().Length < 250)
{
MergeWriteContent(objcells, numchar((cel1).ToString()) + value.ToString(),
numchar((cell2).ToString()) + value.ToString(), oSheet,
drd[m][0].ToString() + " " +
" ",
10, "false", "left", 35, 30, "Left,Bottom,Right,Top#");
}
else if (drd[m][0].ToString().Length > 400)
{
MergeWriteContent(objcells, numchar((cel1).ToString()) + value.ToString(),
numchar((cell2).ToString()) + value.ToString(), oSheet,
drd[m][0].ToString(), 10, "false", "left", 30, 40,
"Left,Bottom,Right,Top#");
}
else
{
MergeWriteContent(objcells, numchar((cel1).ToString()) + value.ToString(),
numchar((cell2).ToString()) + value.ToString(), oSheet,
drd[m][0].ToString() + " " +
" ",
10, "false", "Center", 40, 30, "Left,Bottom,Right,Top#");
}
}
}
else { value = 3; }
for (iRow = 0; iRow < DTs.Rows.Count; iRow++)
{
cel1 = 0;
for (iCol = 0; iCol < DTs.Columns.Count; iCol++)
{
int rows = iRow + value + 1;
int cols = iCol + 1;
cel1 = cel1 + 1;
string cell1 = numchar((cel1).ToString());
string headervalue = DTs.Rows[iRow][iCol].ToString();
if (DTs.Rows[iRow][iCol].ToString().Contains("%$& "))
{
headervalue = DTs.Rows[iRow][iCol].ToString().Replace("%$& ", "");
oSheet.Cells[rows, cols] = headervalue.ToUpper();
objcells = oSheet.get_Range(cell1 + rows.ToString(),
cell1 + rows.ToString());
objcells.Borders.Color = System.Drawing.Color.Black.ToArgb();
objcells.Interior.Color = System.Drawing.Color.LightGray.ToArgb();
objcells.Borders[XlBordersIndex.xlEdgeTop].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeBottom].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeLeft].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeRight].LineStyle =
XlLineStyle.xlContinuous;
objcells.Font.Bold = true;
objcells.Font.Underline = true;
}
else
{
string values = DTs.Rows[iRow][iCol].ToString().ToUpper();
try
{
decimal Dvalue = decimal.Parse(values);
if (!DTs.Columns[iCol].ColumnName.Contains("Lead"))
{
if (!DTs.Columns[iCol].ColumnName.ToUpper().Contains("PKG"))
{
values = "'" + Dvalue.ToString("N5");
}
}
}
catch
{
if (values == "*")
{
values = string.Empty;
oSheet.Cells[rows, cols] = string.Empty; ;
objcells = oSheet.get_Range(cell1 + rows.ToString(),
cell1 + rows.ToString());
objcells.Borders.Color = System.Drawing.Color.Black.ToArgb();
objcells.Interior.Color = System.Drawing.Color.DarkRed.ToArgb();
objcells.Borders[XlBordersIndex.xlEdgeTop].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeBottom].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeLeft].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeRight].LineStyle =
XlLineStyle.xlContinuous;
}
else { values = DTs.Rows[iRow][iCol].ToString().ToUpper(); }
}
oSheet.Cells[rows, cols] = values;
objcells = oSheet.get_Range(cell1 + rows.ToString(),
cell1 + rows.ToString());
objcells.Borders.Color = System.Drawing.Color.Black.ToArgb();
objcells.Borders[XlBordersIndex.xlEdgeTop].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeBottom].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeLeft].LineStyle =
XlLineStyle.xlContinuous;
objcells.Borders[XlBordersIndex.xlEdgeRight].LineStyle =
XlLineStyle.xlContinuous;
}
objcells.HorizontalAlignment = Constants.xlLeft;
objcells.VerticalAlignment = Constants.xlTop;
if (DTs.Rows[iRow][iCol].ToString().Length > 20)
{
((Range)oSheet.Cells[rows, cols]).EntireColumn.ColumnWidth = 30;
}
else if (DTs.Rows[iRow][iCol].ToString().Length < 20)
{
if (header == "TEST" && (DDL_Qutationref.SelectedItem.Text.Contains("VOL")))
{
if ("ASYLocation" == DTs.Columns[iCol].ColumnName.ToString() ||
"TotalTestTime" == DTs.Columns[iCol].ColumnName.ToString() ||
"Comment" == DTs.Columns[iCol].ColumnName.ToString() ||
"IndexTime" == DTs.Columns[iCol].ColumnName.ToString() ||
"TestTime" == DTs.Columns[iCol].ColumnName.ToString())
{
oSheet.Cells.ColumnWidth = 15;
}
else { oSheet.Cells.EntireColumn.AutoFit(); }
}
else if (header == "TEST" && (DDL_Qutationref.SelectedItem.Text.Contains("TI")))
{
if ("Comment" == DTs.Columns[iCol].ColumnName.ToString())
{
oSheet.Cells.ColumnWidth = 15;
}
else { oSheet.Cells.EntireColumn.AutoFit(); }
}
else { oSheet.Cells.EntireColumn.AutoFit(); }
}
else { oSheet.Cells.EntireColumn.AutoFit(); }
objcells.Font.Size = 10;
objcells.WrapText = true;
}
}
}
//###################################################################################
public void WritePriceData(_Worksheet oSheet, string topheader,
Microsoft.Office.Interop.Excel.Range objcells, string header, string Customer,
System.Data.DataTable DtResult)
{
Dt = Commonmethod.data("select TblName,FCTRangeID,WRRangeID from SA_WRADDERTBL " +
" where TblName=(select TblName from SA_WRADDER where QuoterefNo='" +
DDL_Qutationref.SelectedItem.Text + "' and WireType='AU')");
objcells = oSheet.get_Range(oSheet.Cells[800, 800], oSheet.Cells[1, 1]);
Microsoft.Office.Interop.Excel.Border topBdr =
objcells.Borders[XlBordersIndex.xlEdgeTop];
Microsoft.Office.Interop.Excel.Border leftBdr =
objcells.Borders[XlBordersIndex.xlEdgeLeft];
Microsoft.Office.Interop.Excel.Border rightBdr =
objcells.Borders[XlBordersIndex.xlEdgeRight];
Microsoft.Office.Interop.Excel.Border bottomBdr =
objcells.Borders[XlBordersIndex.xlEdgeBottom];
int value = 0;
if (Dt.Rows.Count > 0)
{
#region "Header"
string tablename = Dt.Rows[0][0].ToString();
string Factorname = Dt.Rows[0][1].ToString();
string WireName = Dt.Rows[0][2].ToString();
Dt = Commonmethod.data("Select MinThick from SA_WRTHICKRNG where " +
"WRRangeID='" + WireName + "'");
string endvalue = numchar((14 + Dt.Rows.Count).ToString());
CellBox(objcells, "O", 3, endvalue, 5, oSheet);
mergecell(objcells, "O", 3, endvalue, 5, oSheet);
objcells = oSheet.get_Range("O3", endvalue + "3");
objcells.Font.Bold = true;
bottomBdr = objcells.Borders[XlBordersIndex.xlEdgeBottom];
bottomBdr.LineStyle = XlLineStyle.xlContinuous;
WriteContent(objcells, "O3", oSheet, "Wire Diameter (MIL)", 5, "false", "Center",
15, 12, "Continuous");
#endregion
#region "Load WireThickness"
//-------------wire thickness-----------------------
string Content = string.Empty;
System.Data.DataTable dtwr = new System.Data.DataTable();
dtwr.Columns.Add("Wire");
System.Data.DataTable dtprice = new System.Data.DataTable();
dtprice.Columns.Add("Table");
dtprice.Columns.Add("Pkg");
for (int h = 5, m = 0; h < (5 + Dt.Rows.Count); h++, m++)
{
DataRow dr = dtwr.NewRow();
dr["Wire"] = Dt.Rows[m][0].ToString();
dtwr.Rows.Add(dr);
dtprice.Columns.Add(Dt.Rows[m][0].ToString());
}
WireThickness(dtwr, oSheet, "", objcells, 15, 4);
#endregion
#region "Load FactorRange & Price"
System.Data.DataTable Dt1 = Commonmethod.data("select Factor from SA_WRFCT " +
"where CustCode='' and CustGroup='TI01' and EndDate = (select MAX(EndDate) " +
"from SA_WRFCT where CustCode='' and CustGroup='TI01') order by EndDate desc");
System.Data.DataTable Dt2 = Commonmethod.data("select distinct(PkgGrpID) " +
"from SA_WRPKGGRP where TblName='" + tablename + "'");
int h1 = Dt2.Rows.Count;
int rows = 5;
System.Data.DataTable DtTablevalue = new System.Data.DataTable();
rows = 5;
for (int i1 = 0; i1 < Dt1.Rows.Count; i1++)
{
CellBox(objcells, "M", rows, endvalue, (rows + h1), oSheet);
rows = rows + h1 + 2;
}
rows = 5;
System.Data.DataTable Dt3 = Commonmethod.data("select PkgGrpID,Factor," +
"MinThick,UnitPrice from SA_WRADDERDTL where TblName='" + tablename + "'");
prices = new string[dtprice.Columns.Count];
#region "LoadPrice"
for (int i2 = 0; i2 < Dt1.Rows.Count; i2++)
{
if (i2 != 0)
{
drprice = dtprice.NewRow();
dtprice.Rows.Add(drprice);
drprice = dtprice.NewRow();
dtprice.Rows.Add(drprice);
}
for (int i3 = 0; i3 < Dt2.Rows.Count; i3++)
{
if (i3 == 1) { prices[0] = "TABLE " + (i2 + 1).ToString(); }
else if (i3 == 2)
{
prices[0] = "Gold Price US$" + Dt1.Rows[i2][0].ToString() + "/OZ";
}
else { prices[0] = string.Empty; }
prices[1] = Dt2.Rows[i3][0].ToString();
//for (int i4 = 0; i4 < Dt.Rows.Count; i4++)
//{
// DataRow[] drs3 = Dt3.Select("PkgGrpID='" + Dt2.Rows[i3][0].ToString() +
// "' and Factor='" + Dt1.Rows[i2][0].ToString() + "' and MinThick='" +
// Dt.Rows[i4][0].ToString() + "'");
// if (drs3.Length > 0)
// {
// if (drs3[0]["UnitPrice"].ToString() != "0.00000")
// {
// string valuesp = drs3[0]["UnitPrice"].ToString();
// if (drs3[0]["UnitPrice"].ToString() != "0.00000")
// {
// valuesp = drs3[0]["UnitPrice"].ToString();
// }
// else { prices[i4] = "N/A"; }
// prices[i4 + 2] = valuesp;
// }
// else { prices[i4 + 2] = "N/A"; }
// }
//}
drprice = dtprice.NewRow();
dtprice.Rows.Add(prices);
rows = rows + 1;
}
rows = rows + 2;
}
WirePrice(dtprice, oSheet, "", objcells, 5, 13);
#endregion
//int rowvaluespkg = rows - 1;
int rowvaluespkg = 6;
System.Data.DataTable Dt9 = Commonmethod.data("select PkgGrpID,PkgGroup," +
"PkgLead,PkgFamily,PkgSize from SA_WRPKGGRP where TblName='" +
tablename + "'");
string goldpkgdetail = string.Empty;
rows = rowvaluespkg;
rows = rows - 1;
#endregion
#region "LoadPackageGroupID"
//System.Data.DataTable Dt2 = Commonmethod.data("select distinct(PkgGrpID) " +
// "from SA_WRPKGGRP where TblName='" + tablename + "'");
//System.Data.DataTable Dt9 = Commonmethod.data("select PkgGrpID,PkgGroup," +
// "PkgLead,PkgFamily,PkgSize from SA_WRPKGGRP where TblName='" +
// tablename + "'");
//string goldpkgdetail = string.Empty;
//int rows = 5;
for (int hh0 = 0; hh0 < Dt2.Rows.Count; hh0++)
{
DataRow[] findtable = Dt9.Select("PkgGrpID='" +
Dt2.Rows[hh0][0].ToString() + "'");
if (findtable.Length > 0 && hh0 != Dt2.Rows.Count)
{
for (int hj0 = 0; hj0 < findtable.Length; hj0++)
{
if (findtable[hj0][1].ToString() != string.Empty)
{
goldpkgdetail += findtable[hj0][1].ToString() + ", ";
}
if (findtable[hj0][2].ToString() != string.Empty &&
findtable[hj0][2].ToString() != "0")
{
goldpkgdetail += findtable[hj0][2].ToString() + ", ";
}
if (findtable[hj0][3].ToString() != string.Empty)
{
goldpkgdetail += findtable[hj0][3].ToString() + ", ";
}
if (findtable[hj0][4].ToString() != string.Empty)
{
goldpkgdetail += findtable[hj0][4].ToString() + ", ";
}
if (hj0 == findtable.Length - 2)
{
goldpkgdetail = goldpkgdetail.Remove(goldpkgdetail.Length - 1);
goldpkgdetail += " & ";
}
}
if (goldpkgdetail.Contains("ALL OTHER PACKAGES"))
{
goldpkgdetail = "ALL OTHER PACKAGES,";
}
else
{
if (goldpkgdetail != string.Empty)
{
goldpkgdetail = goldpkgdetail.Remove(goldpkgdetail.Length - 1) +
" families,";
}
}
if (goldpkgdetail != string.Empty)
{
goldpkgdetail = goldpkgdetail.Remove(goldpkgdetail.Length - 1);
}
}
mergecell(objcells, "O", rows, endvalue, rows, oSheet);
//WriteContent(objcells, "N" + (rows).ToString(), oSheet, Dt2.Rows[hh0][0].ToString(),
// 9, "false", "Center", 15, 14, "Left,Bottom,Right,Top#");
if (hh0 == 0)
{
WriteContent(objcells, "E" + (rows).ToString(), oSheet, "A = Assy Only",
9, "false", "Center", 15, 14, "Left,Bottom,Right,Top#");
}
else if (hh0 == 1)
{
WriteContent(objcells, "E" + (rows).ToString(), oSheet, "T = Test Only",
9, "false", "Center", 15, 14, "Left,Bottom,Right,Top#");
}
else if (hh0 == 2)
{
WriteContent(objcells, "E" + (rows).ToString(), oSheet, "K = Turnkey",
9, "false", "Center", 15, 14, "Left,Bottom,Right,Top#");
}
MergeWriteContent(objcells, "O" + (rows).ToString(), "U" + (rows).ToString(), oSheet,
goldpkgdetail, 9, "false", "Left", 15, 14, "Left,Bottom,Top");
rows = rows + 1;
goldpkgdetail = string.Empty;
}
value = rows;
#endregion
PriceDatavalue(DtResult, oSheet, string.Empty, objcells, string.Empty, "11");
}
}