c# - How to programatically convert the text file to a custom made specific excel format with nice tabulated grid

Question

I would like to convert my text file https://imageshack.com/i/eyRUOp6fj to a specific excel format as shown here https://imageshack.com/i/iqVnLA9vj. How can i do this.

In the text file, there are number of components which has same location name. So basically I want to cluster all components with same location name to one row, with total number of components in the 2nd column and so on. Please guide me how can i do this.

Code Snippet:

 string[] lines = File.ReadAllLines(@"c:leh	estdata.txt");
 List<Item> allItems = new List<Item>(lines.Length);
 Dictionary<string, List<Item>> itemsByLocation = new Dictionary<string, List<Item>>   (StringComparer.OrdinalIgnoreCase);
// loop the file, start at 1 assuming headings first row
for (int i = 1; i < lines.Length; i++)
{
// nothing interesting here, just parsing the file
string[] columns = lines[i].Split(new char[] { ';', ',' });                
Item item = new Item() { 
    Designator = columns[ORDINAL_DESIGNATOR], 
    MaxPn = columns[ORDINAL_MAXPN], 
    Footprint = columns[ORDINAL_FOOTPRINT], 
    Location = columns[ORDINAL_LOCATION] };

allItems.Add(item);
List<Item> itemsForThisKey = null;
if (itemsByLocation.TryGetValue(item.Location, out itemsForThisKey) == false)
{
    // we don't already have this location in the dictionary, add it
    itemsForThisKey = new List<Item>();
    itemsByLocation.Add(item.Location, itemsForThisKey);
}
itemsForThisKey.Add(item); // add this item to the relevant grouping

}

I am getting error in Item Error 1 The type or namespace name 'Item' could not be found (are you missing a using directive or an assembly reference?) Also, error for Error 7 The name 'ORDINAL_DESIGNATOR' does not exist in the current context Any helps please!!! Thanks a lot.

Answer 1

This is the pastie of your full code

Create a class to store the particular row of excel sheet. Then assign the data in the loop.

public class ExcelData
{
    public int SrNo { get; set; }
    public int Total { get; set; }
    public List<string> Designator { get; set; }
    public string Comment  { get; set; }
    public string Footprint  { get; set; }
    public string Location  { get; set; }

    public ExcelData()
    {
        Designator = new List<string>();
    }
}

And add the following code in the loop to generate excel data.

List<ExcelData> lstExcel = new List<ExcelData>();
ExcelData fline = null;
for (int i = 0; i < strLines.Length; i++)
{
    line = RemoveWhiteSpace(strLines[i]).Trim();
    if (line.Length == 0)
        continue;
    string[] cells = line.Replace(""", "").Split('	');

    if (i > 0)
    {
        if (cells[1] != LastComment)
        {
            if (fline != null)
                lstExcel.Add(fline);
            fline = new ExcelData();
            fline.SrNo++;
            fline.Footprint = cells[2].Replace(" ", "_");
            fline.Comment = cells[1].Replace(" ", "_");


            iCarousel++;
            if (iCarousel > 45)
                iCarousel = 1;
            LastComment = cells[1];
            fline.Location = String.Format("{0}:{1}", CarouselName, iCarousel);
        }

        fline.Designator.Add(cells[0].Replace(" ", "_"));                        
        fline.Total++;
    }
}
ExportInExcel(lstExcel, @"D:myExcel.xls");

Also create a new function that exports the data into excel file.

NOTE: to use this method you have to add reference for Microsoft Excel Object Library. Project->Add Reference->COM->Microsoft Excel XX.X Object Library

private void ExportInExcel(List<ExcelData> lstData, string excelPath)
{
    Microsoft.Office.Interop.Excel.Application xlApp;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


    xlWorkSheet.Cells[1, 1] = "Sr No.";
    xlWorkSheet.Cells[1, 2] = "Total";
    xlWorkSheet.Cells[1, 3] = "Designator";
    xlWorkSheet.Cells[1, 4] = "Comment";
    xlWorkSheet.Cells[1, 5] = "Footprint";
    xlWorkSheet.Cells[1, 6] = "Location";

    for (int i = 0; i < lstData.Count; i++)
    {
        //i+2 : in Excel file row index is starting from 1. It's not a 0 index based collection
        xlWorkSheet.Cells[i + 2, 1] = (i + 1).ToString();
        xlWorkSheet.Cells[i + 2, 2] = lstData[i].Total.ToString();
        xlWorkSheet.Cells[i + 2, 3] = String.Join(",",lstData[i].Designator.ToArray());
        xlWorkSheet.Cells[i + 2, 4] = lstData[i].Comment;
        xlWorkSheet.Cells[i + 2, 5] = lstData[i].Footprint;
        xlWorkSheet.Cells[i + 2, 6] = lstData[i].Location;

    }

    xlWorkBook.SaveAs(excelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

//This function is created to release the excel class object.
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}