Read from excel and save problem

My problem is when I read from a excel and make some change there, I can not save those changes.

my code lists as below

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();

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair16y ago

please re-write and try below line...


WorkBooks.open(string Filename, object UpdateLinks, object ReadOnly, object Format, 
               object Password, object WriteResPassword, object ReadOnlyRecommend, 
               object Origin, object Delimiter, object Editable, object Notify, 
               object Converter, object AddToMru, object Local, object CorruptLoad)
 
xlWorkBook = xlApp.Workbooks.Open(@filePath, 0, false, 0, "", "", false, 
               Excel.XlPlatform.xlWindows, "",  true, false, 0, 
            true, false, false);

Yili Li16y ago
if (!File.Exists(filePath))
   {
   xlWorkBook.SaveAs(filePath, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
   }
   xlWorkBook.Save();

If the file has already exist. I wont use .SaveAs method. I have xlworkBook.Save();
Suthish Nair16y ago
.SaveAs ==> First time you saving the file..
.Save ==> updating a file...