How to populate an excel document using c#?

Question

excel

I climbed in Google and didn’t understand anything + most of it sends to the Word + examples that I found, when filling out they give an error.
In general, the whole point is this: I need to fill in a couple of lines with text, then display a table below, then a couple of lines of text and send it to print (all this in silent mode).
I figured out how to create a document. But how to fill it in correctly - I can not find information anywhere. I use this - Microsoft.Office.Interop.Excel.
Actually how can this be done? msdn also leafed through - did not find anything intelligible.
I would just like an example of how to fill in the specified cell of the specified string. Well, if possible, how to send it to print immediately.
I found an example where a specific cell was prescribed at once and what value to give it:
cell[1,1] = "text";
But after it the program crashes with an unhandled exception.

Answer 1

I used SpreadsheetLight
Installed via Nuget, there is a nuance, which, although it is written in the package description, but I somehow missed it and suffered for a long time, it requires installation (also via Nuget) of OpenXml, and version 2.0 (not 2.5) therefore, when installing OpenXml in Nuget select package version 1.0 then OpenXml 2.0 will be installed.

Answer 2

If you use Microsoft.Office.Interop.Excel, then so that there are no brakes when filling, transfer the data immediately with a range. Here is one of my methods that I use.

void ToExcel(DataTable dt1)
        {
            try
            {
                Excel.Application EoXL;
                Excel._Workbook EoWB;
                Excel._Worksheet EoSheet;
                Excel.Range excelRange;
                EoXL = new Excel.Application();
                EoXL.Visible = false;
                EoWB = EoXL.Workbooks.Add(Type.Missing);

                int TabRows = 1;

                EoSheet = (Excel.Worksheet)EoWB.Worksheets.get_Item(1);//ссылка на лист excel
                EoSheet.Name = "Отчет о кодах возвратных накладных";
                EoSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

                int row = dt1.Rows.Count;
                int col = dt1.Columns.Count;


                EoSheet.Cells[1, 1] = "Префиксы возвратных накладных и счетов фактур подразделений";
                EoSheet.Cells[1, 1].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                EoSheet.Cells[1, 1].Font.Bold = true;
                EoSheet.Cells[1, 1].Font.Size = 16;

              

                // передаем первую таблицу, заполняем ее в памяти и передаем целиком
                object[,] dataExport = new object[row, col];

                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < col; j++)
                    {
                        dataExport[i, j] = dt1.Rows[i][j];
                    }

                }


                excelRange = EoSheet.Range[EoSheet.Cells[2 + TabRows, 1], EoSheet.Cells[row + 1 + TabRows, col]];
                excelRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, dataExport);
                excelRange.Borders.ColorIndex = 0;

//этот кусок в качестве примера указания типа данных в ячейках
                // excelRange = EoSheet.Range[EoSheet.Cells[2 + TabRows, 8], EoSheet.Cells[row + 1 + TabRows, 10]];
                // excelRange.NumberFormat = "#,##0.00";

                // формируем заголовок
                ArrayList displayColumnExsel = new ArrayList();


                foreach (DataColumn c in dt1.Columns)
                {

                    displayColumnExsel.Add(c.ColumnName);
                }

                object[] dataExportH = new object[col];
                for (int i = 0; i < col; i++)
                    dataExportH[i] = displayColumnExsel[i];

                excelRange = EoSheet.Range[EoSheet.Cells[1 + TabRows, 1], EoSheet.Cells[1 + TabRows, col]];
                excelRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, dataExportH);
                excelRange.Font.Bold = true;
                excelRange.WrapText = true;
                excelRange.Borders.ColorIndex = 0;
                excelRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                      

               EoXL.Visible = true;
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message, "Ошибка метода переноса таблиц", MessageBoxButton.OK, MessageBoxImage.Error);
            }


        }

Answer 3

I recently used this sourceforge.net/projects/jexcelapi/files/CSharpJExcel lib. solved a specific problem.

Answer 4

I figured out how to write in cells (it remains to learn how to format and add tables)
Here is the code:

Excel.Worksheet ObjWorkSheet;
            Excel.Workbook ObjWorkBook;
            Excel.Application ObjExcel = new Excel.Application();
            ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
            ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1];
            ObjWorkSheet.Cells[1, 1] = "фывв";
            ObjWorkSheet.Cells[1, 2] = "фыв";
            ObjExcel.Visible = true;
            ObjExcel.UserControl = true;

It remains to figure out how to print

Answer 5

Through "this" Microsoft.Office.Interop.Excel documents are formed for quite a long time (if they are voluminous), it's better to use OpenXml though it's quite difficult to understand it, so in most cases I use DocX for Excel ClosedXml for Word . You can install via Nuget.

Answer 6

Let me ask - why exactly Excel? Do you just need to print or save the file itself?
1. If the format is not important (.xlsx) and opening in Excel is important, then you can write a text file in .csv or .html format - Excel will be able to open them and display them correctly. I don't know about printing from c# such files.
2. If you just need to print a document with a table in a convenient form and you have a DataSet, then you can do it using ReportingServices. The theme from scratch is not the easiest, but the design of documents and their printing are easier / functional there - for example, you can file charts / graphs and save them in PDF.