I am currently working on a project that records data to Excel from C#. I am using the Office Interop Excel library. I currently have all the data correct and stored into 1 worksheet. I would like the project to make new excel Worksheet every week. Store the data in a new worksheet by week. Here is what I currently have:
//Create new Excel WorkBook
public void CreateExcelWorkBook(string filepath)
{
object misValue = System.Reflection.Missing.Value;
try
{
//xlApp.Visible = true;
if (File.Exists(filepath)) { return; }
else
{
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = xlWorkBook.Worksheets[1];
xlWorkSheet.Name = "Week1";
////Scale Titles
Excel.Range scaleTitle = xlWorkSheet.get_Range("A1:C2");
scaleTitle.Merge();
scaleTitle.Value = "Scale 1";
scaleTitle.Font.Bold = true;
scaleTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
///Add Header Titles
xlWorkSheet.Cells[3, 1] = "Project ID";
xlWorkSheet.Cells[3, 2] = "Scale Weight";
xlWorkSheet.Cells[3, 3] = "Time Stamp";
xlWorkSheet.Cells[3, 4] = "S ID";
Excel.Range headers = xlWorkSheet.get_Range("A3", "I3");
headers = xlWorkSheet.Rows[3];
headers.Interior.Color = Color.Gray;
headers.Font.Color = Color.White;
headers.Font.Bold = true;
xlWorkBook.SaveAs(filepath);
}
}
catch (Exception ex){ MessageBox.Show(ex.Message); }
}
public void AppendAdvise2Excel(string path, string data, string project, string sequence)
{
try
{ /// Determine if Excel Workbook is Open or Not.
if (!IsExcelOpen(path)) { OpenExcel(path); }
xlWorkSheets = xlWorkBook.Worksheets;
xlWorkSheet = xlWorkSheets.get_Item("Week1");
Excel.Range xlRange = xlWorkSheet.UsedRange;
int columnCount = xlRange.Columns.Count;
int rowCount = xlRange.Rows.Count;
int columnRows = GetRowsForColumn(7);
/// Add New Values to their Specific Rows & Columns.
xlWorkSheet.Cells[columnRows + 1, 1] = project;
xlWorkSheet.Cells[columnRows + 1, 2] = data;
xlWorkSheet.Cells[columnRows + 1, 3] = DateTime.Now.ToString();
xlWorkSheet.Cells[columnRows + 1, 4] = sequence;
/// Save the Newly Added Row.
xlWorkBook.Save();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private int GetRowsForColumn(int columnNumber)
{
int columnCount = 0;
try
{
Excel.XlDirection goUp = Excel.XlDirection.xlUp;
xlWorkSheets = xlWorkBook.Worksheets;
xlWorkSheet = xlWorkSheets.get_Item("Week1");
columnCount = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, columnNumber].End(goUp).Row;
}
catch(Exception ex) { MessageBox.Show(ex.Message); }
return columnCount;
}
public void OpenExcel(string filePath)
{
try
{
if (File.Exists(filePath))
{ // File is created and already opened
if (IsExcelOpen(filePath)) { return; }
else
{ // File is created but not open, Opening now.
xlWorkBook = xlApp.Workbooks.Open(filePath);
xlWorkSheets = xlWorkBook.Worksheets;
xlWorkSheet = xlWorkSheets.get_Item(1);
}
}
else
{ // File is NOT created, Call Create()
CreateExcelWorkBook(filePath);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//MessageBox.Show("Error Opening/Creating Excel work book.");
}
}
// Check if Excel is Open or Not.
public bool IsExcelOpen(string filepath)
{
bool isValid = true;
try
{ //System.Runtime.InteropServices.COMException: 'Operation unavailable (Exception from HRESULT: 0x800401E3 (MK_E_UNAVAILABLE))'
xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
if (File.Exists(filepath)) {lApp.Workbooks.get_Item("Scale_Data"); }
else { isValid = false; }
}
catch (Exception ex) { isValid = false; }
return isValid;
}
HERE is my SOLUTION! where Worksheet is the Month Number.
public void AppendExcelAdvise2(string filePath, string data, string po, string product, string id, string item, int worksheet)
{
try
{ /// Determine if Excel Workbook is Open or Not.
if (!IsExcelOpen(filePath, worksheet))
{
OpenExcel(filePath, worksheet);
}
xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
/// Get the Current WorkBook and WorkSheet.
xlWorkSheets = xlWorkBook.Worksheets;
xlWorkSheet = xlWorkSheets.get_Item(worksheet);
int columnRows = GetRowsForColWS(12, worksheet);
//int columnRows = GetRowsForColumn(12);
/// Add New Values to their Specific Rows & Columns.
// Production Order #
xlWorkSheet.Cells[columnRows + 1, 8] = po;
User contributions licensed under CC BY-SA 3.0