I have button which reference to this code:
private void button24_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=" System.Configuration.ConfigurationManager.AppSettings["server"] ";";
connectionString = "Initial Catalog=" System.Configuration.ConfigurationManager.AppSettings["database"] ";";
connectionString = "Integrated Security=True";
Excel.Application oXL = new Excel.Application();
oXL.Visible = true;
//oXL.ActiveSheet.Name = "Je Anaylsis Tests";
object[] Checked = new object[10];
if(checkBox1.Checked)
{
dt = new DataTable();
conn = new SqlConnection(connectionString);
conn.Open();
string _tbName;
_tbName = "Test1";
string _Client;
_Client = "Client_" ClientRowId;
sda = new SqlDataAdapter(@"SELECT * From " _Client ".DBO." _tbName, conn);
sda.Fill(dt);
Checked [0] = "Test1";
Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
ExportExcels.InsertIntoExcelWorksheets( oWB, dt, Checked[0].ToString());
}
if (checkBox2.Checked)
{
dt = new DataTable();
conn = new SqlConnection(connectionString);
conn.Open();
string _tbName;
_tbName = "Test2";
string _Client;
_Client = "Client_" ClientRowId;
sda = new SqlDataAdapter(@"SELECT * From " _Client ".DBO." _tbName, conn);
sda.Fill(dt);
Checked[1] = "Test2";
Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
ExportExcels.InsertIntoExcelWorksheets( oWB, dt, Checked[1].ToString());
}
oXL.UserControl = true;
}
ExportExcels code and InsertIntoExcelWorksheets:
static string[] stRange = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "aa", "ab", "ac", "ad", "ae", "af", "ag", "ah", "ai", "aj", "ak", "al", "am", "an", "ao", "ap", "aq", "ar", "as", "at", "au", "av", "ax", "ay", "az", "ba", "bb", "bc", "bd", "be", "bf", "bg", "bh", "bi", "bj", "bk", "bl", "bm", "bn", "bo", "bp", "bq", "br", "bs", "bt", "bu", "bv", "bx", "by", "bz" };
static string[,] GetMatrixFromDataTable(DataTable dt)
{
string[,] toBeReturn = new string[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i )
for (int j = 0; j < dt.Columns.Count; j )
toBeReturn[i, j] = dt.Rows[i][j].ToString();
return toBeReturn;
}
public static void InsertIntoExcelWorksheets( Excel._Workbook oWB, DataTable dt, string sheetName)
{
Excel._Worksheet oSheet;
//Excel._Worksheet oSheet2;
Excel.Range oRng;
try
{
//oXL.Visible = true;
//Get a new workbook.
oWB.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = sheetName.Substring(0, Math.Min(31, sheetName.Length));
int i = 1;
foreach (DataColumn dc in dt.Columns)
{
oSheet.Cells[1, i] = dc.ColumnName;
i ;
}
oSheet.get_Range("A1", stRange[i - 2] "1").Font.Bold = true;
oSheet.get_Range("A1", stRange[i - 2] "1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
string[,] dtMatrix = GetMatrixFromDataTable(dt);
oSheet.get_Range("A2", stRange[i - 2] (dt.Rows.Count 1).ToString()).Value2 = dtMatrix;
oSheet.DisplayRightToLeft = true;
oSheet.get_Range("A1", stRange[i - 2] (dt.Rows.Count 1).ToString()).Font.Name = "David";
oSheet.get_Range("A1", stRange[i - 2] (dt.Rows.Count 1).ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", stRange[i - 2] (dt.Rows.Count 1).ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("A1", stRange[i - 2] "1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue);
oRng = oSheet.get_Range("A1", stRange[i - 2] (dt.Rows.Count 1).ToString());
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
oRng.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
// oSheet.get_Range("A1", stRange[i - 1] "1").Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle =
oRng = oSheet.get_Range("A1", stRange[i - 2] "1");
oRng.EntireColumn.AutoFit();
//Manipulate a variable number of columns for Quarterly Sales Data.
// DisplayQuarterlySales(oSheet);
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
I want that in the second if it will work on the same excel that already opened before in the first if but it always open new one. In buttom line one excel file with two sheets and not two excels files with two sheets. How can I do that?
Thanks a lot.
CodePudding user response：
You can extract common code of the two if in little helper method:
private void InsertIntoWorkbook(Excel._Workbook oWB, SqlConnection conn, string tableName)
{
string _Client = "Client_" ClientRowId;
var sda = new SqlDataAdapter($"SELECT * From {_Client}.DBO.{tableName}", conn);
var dt = new DataTable();
sda.Fill(dt);
ExportExcels.InsertIntoExcelWorksheets(oWB, dt, tableName);
}
Now, you can reuse the same Excel object and the same connection in the two ifs. The workbook is created once before the checkbox ifs (this fixed the problem):
private void button24_Click(object sender, EventArgs e)
{
string connectionString = $"Data Source={System.Configuration.ConfigurationManager.AppSettings["server"]};";
connectionString = $"Initial Catalog={System.Configuration.ConfigurationManager.AppSettings["database"]};";
connectionString = "Integrated Security=True";
Excel.Application oXL = new Excel.Application();
oXL.Visible = true;
//oXL.ActiveSheet.Name = "Je Anaylsis Tests";
object[] Checked = new object[10];
if (checkBox1.Checked || checkBox2.Checked) {
var oWB = (Excel._Workbook)oXL.Workbooks.Add(Missing.Value);
using (var conn = new SqlConnection(connectionString)) {
conn.Open();
if (checkBox1.Checked) {
string tableName = "Test1";
InsertIntoWorkbook(oWB, conn, tableName);
Checked[0] = tableName;
}
if (checkBox2.Checked) {
string tableName = "Test2";
InsertIntoWorkbook(oWB, conn, tableName);
Checked[1] = tableName;
}
}
}
oXL.UserControl = true;
}
Putting the connection object in a using statement ensures that the connection will be closed at the end of the block.
String interpolation simplifies string creation.
You are using global variables at some places where it seems that local variables would be appropriate.
The Checked array should be of type string[] since it contains table names. However, I do not see the purpose of it, since you never read from it. Also, why does it have a length of 10 when only two names are inserted?