-1

enter image description here Hello. What i want to achieve is to loop through the leftmost column until i find a non-empty cell. When i find one, i want to store all the rows until i find another non-empty cell in the left most column. The result must be stored in a seperate .csv file.

Referencing to the added picture. I want to have a .csv file made named john. in the John.csv file i want rows 1,2 and 3.

And continue this trend until there is nothing left in the worksheet. If you'd use the input in the picture, i'd have a John.csv file with rows 1,2 and 3 in them, and a komar.csv file with rows 4 and 5 in them.

Im quiet stuck on how i can achieve this result.

brent schoenmakers
  • 49
  • 3
  • How do you access Excel? With a 3-party-tool? Some code you use until will be helpfull... – PBum Nov 12 '20 at 13:17
  • I use the Microsoft Interop Excel libary to open a specific worksheet in my workbook. https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel?view=excel-pia – brent schoenmakers Nov 12 '20 at 13:19
  • 1
    Interop is not an efficient way to interact with an Excel file - you don't really want to use the whole Excel application, when you could work directly with the file data instead. Why not use something like NPOI or ClosedXML – ADyson Nov 12 '20 at 13:25

1 Answers1

-1

This will do what you want. Just add this line in a button click to kick it off (with your own path and filename and sheet name, of course):

ProcessFile(@"c:\temp\", "book2.xlsx", "sheet1");

And put the code below in a form or a class or where ever you want it:

using Excel = Microsoft.Office.Interop.Excel;

    private void ProcessFile(string folderPath, string inputFileName, string inputSheetName)
    {

        string outputFileName = "";//will change for each file

        object[,] data = ReadWorksheetFromExcel(folderPath + inputFileName, inputSheetName);
        StringBuilder sbRow = new StringBuilder();
        string comma = "";
        StringBuilder sbFile = new StringBuilder();

        for (int row = 1; row <= data.GetLength(0); row++)
        {
            if (data[row, 1] != null && data[row, 1].ToString().Trim() != "")
            {
                if (sbFile.ToString() != "")
                {
                    //write previous name to a file
                    System.IO.File.WriteAllText(folderPath + outputFileName, sbFile.ToString());
                }
                //save the value of column 1 to use as the
                //file name once all the data is read for this file
                outputFileName = data[row, 1].ToString().Trim() + ".csv";
                sbFile.Clear();
            }
            for (int col = 2; col <= data.GetLength(1); col++)
            {
                if (data[row, col] != null && data[row, col].ToString() != "")
                {
                    string value = data[row, col].ToString();
                    sbRow.Append(comma + value);
                    comma = ",";
                }
            }
            sbFile.AppendLine(sbRow.ToString());
            sbRow.Clear();
            comma = "";
        }
        if (sbFile.ToString() != "")
        {
            //write final name to a file
            System.IO.File.WriteAllText(folderPath + outputFileName + ".csv", sbFile.ToString());
        }
    }
    public object[,] ReadWorksheetFromExcel(string ExcelFilePath, string WorksheetName)
    {
        var excel = new Excel.Application();
        excel.DisplayAlerts = false;
        excel.Visible = true;

        Excel.Workbook workbook = excel.Workbooks.Open(ExcelFilePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        Excel.Worksheet worksheet = workbook.Worksheets[WorksheetName];

        Excel.Range range = worksheet.UsedRange;
        object[,] values = (object[,])range.Value2;

        return values;
    }
JesseChunn
  • 555
  • 3
  • 7
  • 1
    Please don't write answers that do all the work for the person asking. This encourages people to ask for all their code to be written for them, which is not what Stack Overflow is for. – Ian Kemp Nov 12 '20 at 17:55