0

I am working on requirement to read data from DB(from an array(String[] getPersons) as input) and write it to excel file. I struck with one scenario where in DB, I am having DepartmentID value as 1,2,3 e.t.c. Where 1 is for CSE,2 for ECE,3 for IT e.t.c. Now, the below code is fetching the numbers for DepartmentId and instead i need to have respective departments for the numbers.


var container = db.GetContainer(containerId);
var q = container.GetItemLinqQueryable<Student>();
var requests = q.Where(p => getStudents.Contains(p.StudentName)).ToFeedIterator();

using (var ms = new MemoryStream())
  {
TextWriter tw = new StreamWriter(ms);
 foreach (var request in requests)
                {
                    tw.WriteLine($"{request.DepartmentId},{request.StudentName}");

                }
                tw.Flush();           
                await ms.CopyToAsync(response.FileContent).ConfigureAwait(false);
   }

Please note that, There is no field in DB for DepartmentName(which cannot be added as well) and i need to somehow convert DepartmentId to respective DepartmentNames and then write it to excel. Is there a way to do that? I am open to suggestions. Thanks in Advance!!!

tartar
  • 140
  • 8
  • Excel files aren't text, they are ZIP packages containing XML files. You can't use `TextWriter` to write to any binary file, not just Excel files. You can use a library like EPPlus or NPOI to create an Excel file from a collection with eg `sheet.Cells.LoadFromCollectioon(requests)` – Panagiotis Kanavos Jan 27 '21 at 15:45
  • Seems like you need to specify a bit more about the excel part. In my answer your code will create the same file as before but with the correct department name. It will still be comma separeted (csv) which can be opened by excel. If you want it to be a xls/xlsx file they are in different formats (the new one is a zip package as mentioned by Panagiotis) but not the older ones. Please specify a bit more and we can help you better. – MrApnea Jan 27 '21 at 15:52

1 Answers1

1

If you have all departments you could always hardcode them if they are not available in another way.

Create a method for translation:

    private static string GetDepartmentName(int id)
    {
        switch(id)
        {
            case 1:
                return "CSE";
            case 2:
                return "ECE";
            case 3:
                return "IT";
            default:
                throw new NotImplementedException("No such id!");
        }
    }

Then you can use that in your code:

        using (var ms = new MemoryStream())
        {
            TextWriter tw = new StreamWriter(ms);
            foreach (var request in requests)
            {
                tw.WriteLine($"{GetDepartmentName(request.DepartmentId)},{request.StudentName}");

            }
            tw.Flush();
            await ms.CopyToAsync(response.FileContent).ConfigureAwait(false);
        }

Then just add all the other departments in the "GetDepartmentName" method.

Hope this works, and if not then please let me know what I missed.

MrApnea
  • 1,776
  • 1
  • 9
  • 17
  • You can't use `TextWriter` to write to an Excel file. Excel files are ZIP packages – Panagiotis Kanavos Jan 27 '21 at 15:47
  • I was guessing he wrote it to an CSV file which is an easy way to write files that can be opened in excel. There are multiple versions of excel-files, the new ones are zip but not the old format. Since no specification was made I thought he wanted to use CSV. – MrApnea Jan 27 '21 at 15:49
  • A CSV file is not an Excel file at all. The current format is already 14 years old. It's also the only Excel format supported for free by cloud providers like Google or even Microsoft, the only format that can be generated easily on a web server. The "old" format isn't old, it's obsolete – Panagiotis Kanavos Jan 27 '21 at 15:50
  • I hear you. But to be fair he never mentioned that is should be in a excel format. He wanted the result to excel. An easy was to do that (which is still heavily used today) are csv files. I would personally prefer the xslx format but in many older systems you don't have that choice. – MrApnea Jan 27 '21 at 15:55
  • Exporting to CSV isn't that easy. What happens if the fields contain commas? What about number and date formats? Non-English text? There are a *lot* of questions asking why Excel can't open a CSV file by double-clicking and displays an entire row in a single cell, or mangles the text because it doesn't match the current locale – Panagiotis Kanavos Jan 27 '21 at 16:11