My problem is when I read from a excel and make some change there, I can not save those changes.
My logic is: if the excel is not exist, I am going to "create" a spreadsheet and there is no problem here.
The problem is here, if the object file is already exist. I am going to "Open" the excel spreadsheet and make some change inside.
After that, I want to save the change to the original document. But it doesn't work.
My rCount is a static parameter, I have decleared it in other place.
private void insertReport1(DateTime TimeFrame)
{
SqlConnection MyConnection = new SqlConnection();
string filePath = this.textBox3.Text;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
// FileInfo destination = new FileInfo(filePath);
// StreamWriter Tex;
SqlCommand cmd;
if (File.Exists(filePath))
{
xlWorkBook = xlApp.Workbooks.Open(@filePath, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, true, misValue, misValue, misValue, misValue, misValue);
//xlWorkBook = xlApp.Workbooks.Open(@filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Tex = destination.AppendText();
}else
{
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[rCount, 1] = "Date";
xlWorkSheet.Cells[rCount, 2] = "Loc_ID";
xlWorkSheet.Cells[rCount, 3] = "CONS_UPC";
xlWorkSheet.Cells[rCount, 4] = "WD_Code";
xlWorkSheet.Cells[rCount, 5] = "ScnQty";
xlWorkSheet.Cells[rCount, 6] = "Rtl";
xlWorkSheet.Cells[rCount++, 7] = "prod_Desc";
// Tex = destination.CreateText();
// Tex.WriteLine("Date".PadRight(30, ' ') + "Loc_ID".PadRight(20, ' ') + "CONS_UPC".PadRight(20, ' ') + "WD_Code".PadRight(20, ' ') + "ScnQty".PadRight(20, ' ') + "Rtl".PadRight(20, ' ') + "prod_Desc".PadRight(20, ' '));
}
string sqlconStr = global::test.Properties.Settings.Default.testConnectionString;
try
{
MyConnection.ConnectionString = sqlconStr;
MyConnection.Open();
}
catch (Exception)
{
if (MyConnection != null)
MyConnection.Dispose();
MessageBox.Show("Connecting failed", "error message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
string[] date = TimeFrame.ToString().Split(char.Parse(" "))[0].Split(char.Parse("/"));
if (date[0].Length < 2)
date[0] = "0" + date[0];
if (date[1].Length < 2)
date[1] = "0" + date[1];
string tableName = date[2] + "_" + date[0] + "_" + date[1];
string sqlPart1 = "SELECT Date, Loc_ID, CONS_UPC, WD_Code, ScnQty, Rtl, Prod_Desc From [";
string conditionStr = "";
string[] UPCs = this.textBox1.Lines;
if (this.textBox2.Text.Trim() != "")
{
string[] Stores = this.textBox2.Lines;
foreach (string UPC in UPCs)
{
foreach (string Store in Stores)
{
conditionStr = conditionStr + " OR " + "(CONS_UPC = " + UPC.Trim() + ") AND ( Loc_ID = " + Store.Trim() + ")";
}
}
}
else
{
foreach (string UPC in UPCs)
{
conditionStr = conditionStr + " OR " + "(CONS_UPC = " + UPC.Trim() + ")";
}
}
conditionStr = conditionStr.Substring(3, conditionStr.Length - 3);
string sqlCmd = sqlPart1 + tableName + "] Where " + conditionStr + " ORDER BY CONS_UPC, Loc_ID";
cmd = new SqlCommand(sqlCmd, MyConnection);
try
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string DateInfo = reader["Date"].ToString();
string Loc_ID = reader["Loc_ID"].ToString();
string CONS_UPC = reader["CONS_UPC"].ToString();
string WD_Code = reader["WD_Code"].ToString();
string ScnQty = reader["ScnQty"].ToString();
string Rtl = reader["Rtl"].ToString();
string prod_Desc = reader["Prod_Desc"].ToString();
xlWorkSheet.Cells[rCount, 1] =DateInfo;
xlWorkSheet.Cells[rCount, 2] = Loc_ID;
xlWorkSheet.Cells[rCount, 3] = CONS_UPC;
xlWorkSheet.Cells[rCount, 4] = WD_Code;
xlWorkSheet.Cells[rCount, 5] = ScnQty;
xlWorkSheet.Cells[rCount, 6] = Rtl;
xlWorkSheet.Cells[rCount++, 7] = prod_Desc;
// xlWorkBook.Save();
// Tex.WriteLine(DateInfo.PadRight(30, ' ') + Loc_ID.PadRight(20, ' ') + CONS_UPC.PadRight(20, ' ') + WD_Code.PadRight(20, ' ') + ScnQty.PadRight(20, ' ') + Rtl.PadRight(20, ' ') + prod_Desc.PadRight(20, ' '));
}
reader.Close();
}
catch (Exception)
{
MessageBox.Show("Retrieval out of bound error happened, " + TimeFrame + "'s data is not in datebase yet! Program has terminated");
return;
}
// xlApp.DisplayAlerts = false;
if (!File.Exists(filePath))
{
xlWorkBook.SaveAs(filePath, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
xlWorkBook.Save();
xlWorkBook.Close(true, filePath, true);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MyConnection.Close();
// Tex.Close();
}