I'm working on a Windows Form application with no C# experience but want to learn. I want to read an xlsx file, extract data, and write/output the selected data to another spreadsheet.
As you can see from my code, I'm manually writing to the spreadsheet, which is the opposite of what I'm trying to achieve.
I've checked some forums and some recommend Spire but I can't find the reference to add it to my project. I also found that oledb is a great option but I'm not sure how to write it without the long list of errors.
I'm using Visual Studio Community edition v 16.5.4
Any suggestion is appreciated.
What I have tried:
System.Threading.Thread.Sleep(5000);
OpenFileDialog ofd = new OpenFileDialog();
ofd.ShowDialog();
System.Threading.Thread.Sleep(10000);
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\benjamin.johnson\Documents\CACE_Issues_List_2019-53.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if (j == 1)
Console.Write("\r\n");
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
{
Excel.Application excelApp = new Excel.Application();
if (excelApp != null)
{
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add();
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add();
excelApp.Visible = true;
excelWorksheet.Cells[1, 1] = "Opt/Req";
excelWorksheet.Cells[1, 2] = "Type";
excelWorksheet.Cells[1, 3] = "Accepted Date";
excelWorksheet.Cells[1, 4] = "Bug#";
excelWorksheet.Cells[1, 5] = "Title";
excelWorksheet.Cells[1, 6] = "Subsystem";
excelWorksheet.Cells[1, 7] = "Design";
excelWorksheet.Cells[1, 8] = "Segment";
excelWorksheet.Cells[1, 9] = "Release";
excelApp.Columns.AutoFit();
excelWorksheet.Name = "Issues";
if (File.Exists("TestIssueTracker2020.xlsx"))
{
File.Delete("TestIssueTracker2020.xlsx");
}
excelApp.ActiveWorkbook.SaveAs("TestIssueTracker2020", Excel.XlFileFormat.xlWorkbookNormal);
excelWorkbook.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorkbook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
MessageBox.Show("Excel file created, you can find the file on your Documents folder");
}