I am creating Excel sheet from ListViewItem Collection i.e. from System.Windows.Forms.ListView.ListViewItemCollection, You can take any array for this.
So here is my code to generate the excel sheet
public static void createExcel(System.Windows.Forms.ListView.ListViewItemCollection items)
{
//Creating Excel Sheet for Report
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws1 = (Worksheet)wb.Worksheets[1];
ws1.Name = "Failed Result";
Worksheet ws2 = (Worksheet)wb.Worksheets.Add();
ws2.Name = "Success Result";
if (ws1 == null || ws2 == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
int success = 0;
int failed = 0;
for (int i = 1; i <= items.Count; i++)
{
// Fill the cells in the selected range of the worksheet with the number 6.
if (items[i - 1].BackColor == Color.LightSeaGreen)
{
success++;
ws2.Cells[success, 1] = i.ToString();
//This will increase the column width
ws2.Cells[success, 2].EntireColumn.ColumnWidth = 20;
ws2.Cells[success, 2] = items[i - 1].SubItems[1].Text.ToString();
ws2.Cells[success, 3] = items[i - 1].SubItems[2].Text.ToString();
}
else
{
failed++;
ws1.Cells[failed, 1] = i.ToString();
ws1.Cells[failed, 2] = items[i - 1].SubItems[1].Text.ToString();
ws1.Cells[failed, 3] = items[i - 1].SubItems[2].Text.ToString();
//Link Second Sheet Cell link to First Sheet Cell
ws1.Hyperlinks.Add(ws1.get_Range("C" + failed, Type.Missing), "#Sheet1!A"+failed, Type.Missing, "Link to Issue");
}
}
DateTime date = new DateTime();
wb.SaveAs("C:\\data\\Result"+date.ToString("g")+".xlsx");
}
So here is my code to generate the excel sheet
public static void createExcel(System.Windows.Forms.ListView.ListViewItemCollection items)
{
//Creating Excel Sheet for Report
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws1 = (Worksheet)wb.Worksheets[1];
ws1.Name = "Failed Result";
Worksheet ws2 = (Worksheet)wb.Worksheets.Add();
ws2.Name = "Success Result";
if (ws1 == null || ws2 == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
int success = 0;
int failed = 0;
for (int i = 1; i <= items.Count; i++)
{
// Fill the cells in the selected range of the worksheet with the number 6.
if (items[i - 1].BackColor == Color.LightSeaGreen)
{
success++;
ws2.Cells[success, 1] = i.ToString();
//This will increase the column width
ws2.Cells[success, 2].EntireColumn.ColumnWidth = 20;
ws2.Cells[success, 2] = items[i - 1].SubItems[1].Text.ToString();
ws2.Cells[success, 3] = items[i - 1].SubItems[2].Text.ToString();
}
else
{
failed++;
ws1.Cells[failed, 1] = i.ToString();
ws1.Cells[failed, 2] = items[i - 1].SubItems[1].Text.ToString();
ws1.Cells[failed, 3] = items[i - 1].SubItems[2].Text.ToString();
//Link Second Sheet Cell link to First Sheet Cell
ws1.Hyperlinks.Add(ws1.get_Range("C" + failed, Type.Missing), "#Sheet1!A"+failed, Type.Missing, "Link to Issue");
}
}
DateTime date = new DateTime();
wb.SaveAs("C:\\data\\Result"+date.ToString("g")+".xlsx");
}
No comments:
Post a Comment