static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets.Add("Sheet1");

            // Merge cells
            worksheet.Range("B6:D18").Merge();

            // Write text
            worksheet.Cell("C10").MergedWithCell.Value = "Bytescout.Spreadsheet";


            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close document
            document.Close();

            // Open document in default xls viewer
            Process.Start("Output.xls");
        }
        static void Main(string[] args)
        {
            const string fileName = "CSharpImportFromDataTable.xls";

            // Create a new spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            // Get the data from the datatable that we want to import
            DataTable periodicTable = GetDataTable();

            // Import data into spreadheet
            spreadsheet.ImportFromDataTable(periodicTable);

            // Insert row with column captions
            Worksheet worksheet = spreadsheet.Worksheets[0];

            worksheet.Rows.Insert(0);
            for (int colIndex = 0; colIndex < periodicTable.Columns.Count; colIndex++)
            {
                worksheet.Cell(0, colIndex).Value = periodicTable.Columns[colIndex].Caption;
            }

            // Save the spreadsheet
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            spreadsheet.SaveAs(fileName);

            // Close spreadsheet
            spreadsheet.Close();

            // Open the spreadsheet
            Process.Start(fileName);
        }
Esempio n. 3
0
        static void Main(string[] args)
        {
            try
            {
                // create new Spreadsheet object
                Spreadsheet spreadsheet = new Spreadsheet();

                // add new worksheet
                Worksheet sheet = spreadsheet.Workbook.Worksheets.Add("Sample");

                // add a picture to worksheet
                sheet.Pictures.Add("image.jpg", 100, 100);
                sheet.Cell("A1").ValueAsHTML = "<b>Image from</b> from <i>Image.jpg</i>";

                if (File.Exists("Output.xls"))
                {
                    File.Delete("Output.xls");
                }

                // Save it as XLS
                spreadsheet.SaveAs("Output.xls");

                // close the document
                spreadsheet.Close();

                // open output XLS
                Process.Start("Output.xls");
            }
            catch (Exception e) {
                Console.WriteLine("CAN NOT EXECUTE: " + e.ToString());
                Console.WriteLine("\nPress any key to exit");
                Console.ReadKey();
            }
        }
        static void Main(string[] args)
        {
            // Open existing Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("input.xls");

            // Create copy of worksheet
            document.Workbook.Worksheets.Copy(0, 1, "Copy of Sheet1");

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close document
            document.Close();

            // Open document in default xls viewer
            Process.Start("Output.xls");
        }
        static void Main(string[] args)
        {
            // Open Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("SimpleReport.xls");

            // Get Worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];


            // delete output file if exists already
            if (File.Exists("SimpleReport.htm"))
            {
                File.Delete("SimpleReport.htm");
            }

            // Export to HTML
            worksheet.SaveAsHTML("SimpleReport.htm");

            // Close Spreadsheet
            document.Close();

            // open generated HTML
            Process.Start("SimpleReport.htm");
        }
        static void Main(string[] args)
        {
            const string inputFile = @"PeriodicTableOfElementsSpreadsheet.xls";

            // Open and load spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            spreadsheet.LoadFromFile(inputFile);

            // Get the data from the spreadsheet
            DataTable dt = spreadsheet.ExportToDataTable();

            // Close spreadsheet
            spreadsheet.Close();

            // Display array
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j] + " ");
                }
                Console.WriteLine();
            }

            // Pause
            Console.ReadLine();
        }
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("sample.xls");

            // Move page
            document.Workbook.Worksheets.Move(1, 0);

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
        /// <summary>
        /// shows how to convert XML data into XLS excel using Bytescout.Spreadsheet and Bytescout.Spreadsheet.Utils.SimpleXMLConverter
        /// </summary>
        private static void SampleXMLtoXLSConversion()
        {
            // read XML and convert into XLS (Excel) and save

            Spreadsheet document = new Spreadsheet();

            SimpleXMLConverter tools = new SimpleXMLConverter(document);

            tools.LoadXML("AdvancedReport.xml");

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 9
0
        static void Main(string[] args)
        {
            const string fileName = "CSharpImportFromJaggedArray.xls";

            // Create a new spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            // Get the data from the jagged array that we want to import
            string[][] periodicTable = GetJaggedArray();

            // Import data into spreadheet
            spreadsheet.ImportFromJaggedArray(periodicTable);

            // Save the spreadsheet
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            spreadsheet.SaveAs(fileName);

            // Close spreadsheet
            spreadsheet.Close();

            // Open the spreadsheet
            Process.Start(fileName);
        }
Esempio n. 10
0
        static void Main(string[] args)
        {
            // Create new spreadsheet (or open existing)
            Spreadsheet doc = new Spreadsheet();
            // Add worksheet
            Worksheet worksheet = doc.Worksheets.Add();

            // Put background image on the worksheet
            worksheet.BackgroundPicture = Image.FromFile("image1.jpg");


            // Delete output file if exists
            if (File.Exists("output.xls"))
            {
                File.Delete("output.xls");
            }

            // Save document
            doc.SaveAs("output.xls");

            // Close spreadsheet
            doc.Close();

            // Open generated XLS document in default application
            Process.Start("output.xls");

            doc.Dispose();
        }
        static void Main(string[] args)
        {
            const string fileName = "CSharpImportFrom2DArray.xls";

            // Create a new spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            // Get the data from the 2D array that we want to import
            string[,] stockPrices = Get2DArray();

            // Import data into spreadheet
            spreadsheet.ImportFrom2DArray(stockPrices);

            // Save the spreadsheet
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            spreadsheet.SaveAs(fileName);

            // Close spreadsheet
            spreadsheet.Close();

            // Open the spreadsheet
            Process.Start(fileName);
        }
        static void Main(string[] args)
        {
            // Open Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("AdvancedReport.xls");

            // Get Worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Add new Column
            worksheet.Columns.Insert(3, 1);
            // Set values
            worksheet.Rows[0][2].Value = "Age";

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 13
0
        static void Main(string[] args)
        {
            // Create new XLS document
            Spreadsheet document = new Spreadsheet();

            // Create new worksheet
            Worksheet worksheet = document.Workbook.Worksheets.Add();

            // Set column width
            worksheet.Columns[1].Width = 300;
            // Set column height
            worksheet.Rows[1].Height = 100;

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }


            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open in default spreadsheets viewer/editor
            Process.Start("Output.xls");
        }
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Add new worksheet
            Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");

            // Set cell value
            worksheet.Cell(0, 0).Value = "Very, very, very, very long text";

            // Shrink text
            worksheet.Cell(0, 0).ShrinkToFit = true;

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 15
0
        static void Main(string[] args)
        {
            // Open spreadsheet from file
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("example.xls");

            // Get first worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Hide formula in B9 cell
            worksheet.Cell("B9").HiddenFormula = true;
            // Protect the worksheet with password
            worksheet.Protect("password");

            // Delete output file if exists
            if (File.Exists("changed.xls"))
            {
                File.Delete("changed.xls");
            }

            // Save document
            document.SaveAs("changed.xls");

            // Close spreadsheet
            document.Close();

            // Open generated XLS document in default program
            Process.Start("changed.xls");
        }
Esempio n. 16
0
        public static void Writer(List<XlsChanges> changes)
        {
            Spreadsheet document = new Spreadsheet();
            Worksheet Sheet = document.Workbook.Worksheets.Add("Changes");
            Sheet.Cell("A1").Value = "Previous name";
            Sheet.Columns[0].Width = 250;
            Sheet.Cell("B1").Value = "New name";
            Sheet.Columns[1].Width = 250;
            Sheet.Cell("D1").Value = "Previous value";
            Sheet.Columns[3].Width = 250;
            Sheet.Cell("E1").Value = "New value";
            Sheet.Columns[4].Width = 250;

            var i = 2;
            foreach (var change in changes)
            {
                Sheet.Cell("A" + i).Value = change.OldName;
                Sheet.Cell("B" + i).Value = change.NewName;
                Sheet.Cell("D" + i).Value = change.OldValue;
                Sheet.Cell("E" + i).Value = change.NewValue;
                i++;
            }


            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            document.SaveAs("Output.xls");

            document.Close();
        }
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Add new worksheet
            Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");

            // Set cell value
            worksheet.Cell(0, 0).Value = "Hello, World!";

            // Add horizontal page break
            worksheet.HPageBreaks.Add(new CellsRange("A10"));

            // Add vertical page break
            worksheet.VPageBreaks.Add(new CellsRange("F1"));

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // Open generated XLS document in default associated application
            Process.Start("Output.xls");
        }
Esempio n. 18
0
        static void Main(string[] args)
        {
            string[]    arr      = new string[0];
            string[][]  readFile = new string[0][];
            string      pathUrl  = @"C:\Users\TaiPham\Desktop\PrivateIncludes";
            Spreadsheet document = new Spreadsheet();
            Worksheet   Sheet    = document.Workbook.Worksheets.Add("sheet1");

            Sheet.Cell("A1").Value = "Path";
            Sheet.Columns[0].Width = 250;
            Sheet.Cell("B1").Value = "Line";
            Sheet.Columns[1].Width = 250;
            Sheet.Cell("C1").Value = "English";
            Sheet.Columns[2].Width = 250;
            Sheet.Cell("D1").Value = "pathRoot";
            Sheet.Columns[3].Width = 250;

            string[] tes = ReadAllFilesInDirectory(pathUrl, arr);
            Console.WriteLine(pathUrl.Length);
            readFile = IterateFiles(tes, pathUrl.Length);
            int rowIndex = 2;

            for (int i = 0; i < readFile.Length; i++)
            {
                Sheet.Cell(Convert.ToString("A" + rowIndex)).Value = readFile[i][0];
                Sheet.Cell(Convert.ToString("B" + rowIndex)).Value = readFile[i][2];
                Sheet.Cell(Convert.ToString("C" + rowIndex)).Value = readFile[i][1];
                Sheet.Cell(Convert.ToString("D" + rowIndex)).Value = Convert.ToString(pathUrl + @"\" + readFile[i][0]);
                rowIndex++;
            }

            document.SaveAs("Output.xls");
            document.Close();
            Process.Start("Output.xls");
        }
Esempio n. 19
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("Sample.xls");

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");

            // Check dates
            for (int i = 0; i < 4; i++)
            {
                // Set current cell
                Cell currentCell = worksheet.Cell(i, 0);

                DateTime date = currentCell.ValueAsDateTime;

                // Write Date
                Console.WriteLine("{0}", date.ToShortDateString());
            }

            // Close document
            document.Close();

            // Write message
            Console.Write("Press any key to continue...");

            // Wait user input
            Console.ReadKey();
        }
Esempio n. 20
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("Input.xls");

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Unmerge cells
            worksheet.Range("B6:D18").UnMerge();
            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 21
0
        static void Main(string[] args)
        {
            // Open spreadsheet from file
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile(@"example.xls");
            //document.LoadFromFile(@"d:\2\1\Bytescout\Spreadsheet SDK\TestCase\Formats.xls");

            // Get first worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Copy first two ("A-B") columns to the fourth ("D") column
            worksheet.Columns.CopyAndPaste(0, 1, 12);

            // Delete output file if exists
            if (File.Exists("changed.xls"))
            {
                File.Delete("changed.xls");
            }

            // Save document
            document.SaveAs("changed.xls");

            // Close spreadsheet
            document.Close();

            // Open generated XLS document in default program
            Process.Start("changed.xls");
        }
Esempio n. 22
0
        static void Main(string[] args)
        {
            const string inputFile = @"StockPricesSpreadsheet.xls";

            // Open and load spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            spreadsheet.LoadFromFile(inputFile);

            // Get the data from the spreadsheet
            string[,] stockPrices = spreadsheet.ExportTo2DArray();

            // Close spreadsheet
            spreadsheet.Close();

            // Display data in data table
            for (int i = 0; i < stockPrices.GetLength(0); i++)
            {
                for (int j = 0; j < stockPrices.GetLength(1); j++)
                {
                    Console.Write(stockPrices[i, j] + " ");
                }
                Console.WriteLine();
            }

            // Pause
            Console.ReadLine();
        }
Esempio n. 23
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Add new worksheet
            Worksheet worksheet = document.Workbook.Worksheets.Add("Sheet1");

            // Apply rich text formatting
            worksheet.Cell(0, 0).ValueAsHTML = "<b><u>Bold Underline</u>, and <i>bold italic</i></b> text";
            worksheet.Cell(1, 0).ValueAsHTML = "<font color=Blue>Blue</font>, <font color=Green>Green</font> and other <b><font face=Tahoma color=Red>co<font><font face=Tahoma color=Green>lo<font><font face=Tahoma color=Blue>rs<font></b> <font color=black><b>are</b> <u>available</u></font>";

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 24
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Move page
            Worksheet worksheet = document.Workbook.Worksheets.Add();

            // Set unicode text
            worksheet.Cell(0, 0).Value = "鍖鐳鑅";

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
Esempio n. 25
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Add new worksheet
            Worksheet worksheet = document.Workbook.Worksheets.Add("HelloWorld");

            // Set cell value
            worksheet.Cell(0, 0).Value = "Hello, World!";

            if (File.Exists("Output.xlsx"))
            {
                File.Delete("Output.xlsx");
            }

            // Save document
            document.SaveAs("Output.xlsx");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xlsx");
        }
Esempio n. 26
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
               Spreadsheet document = new Spreadsheet();
               document.LoadFromFile("data.xls");

               // Get worksheet by name
               Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");

               // Check dates
               for (int i = 0; i < 4; i++)
               {
               // Set current cell
               Cell currentCell = worksheet.Cell(i, 0);

               DateTime date = currentCell.ValueAsDateTime;

               // Write Date
               Console.WriteLine("{0}", date.ToShortDateString());
               }

               // Close document
               document.Close();

               // Write message
               Console.Write("Press any key to continue...");

               // Wait user input
               Console.ReadKey();
        }
Esempio n. 27
0
        static void Main(string[] args)
        {
            const string inputFile = @"ListOfPlanetsSpreadsheet.xls";

            // Open and load spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();

            spreadsheet.LoadFromFile(inputFile);

            // Get the data from the spreadsheet
            IList planets = new string[10, 10];

            spreadsheet.ExportToList(planets);

            // Close spreadsheet
            spreadsheet.Close();

            // Display array
            string[,] planetsArray = planets as string[, ];
            for (int i = 0; i < planetsArray.GetLength(0); i++)
            {
                for (int j = 0; j < planetsArray.GetLength(1); j++)
                {
                    Console.Write(planetsArray[i, j] + " ");
                }
                Console.WriteLine();
            }

            // Pause
            Console.ReadLine();
        }
Esempio n. 28
0
        static void Main(string[] args)
        {
            // Open Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("AdvancedReport.xls");

            // Get Worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Set cell value
            worksheet.Cell(1, 1).Value = "Homer Jay Simpson 2";

            // delete output file if exists already
            if (File.Exists("AdvancedReport_Modified.xls"))
            {
                File.Delete("AdvancedReport_Modified.xls");
            }


            // Save document
            document.SaveAs("AdvancedReport_Modified.xls");

            // Close Spreadsheet
            document.Close();

            // open output document in default viewer
            Process.Start("AdvancedReport_Modified.xls");
        }
        static void Main(string[] args)
        {
            // Open spreadsheet from file
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("template.xls");

            // Get first worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Find macros and replace them with values
            Replace(worksheet, "<PRODUCT_NAME1>", "Product 1", null);
            Replace(worksheet, "<PRODUCT_NAME2>", "Product 2", null);
            Replace(worksheet, "<PRICE1>", 24.99f, "0.00");
            Replace(worksheet, "<PRICE2>", 29.99f, "0.00");
            Replace(worksheet, "<QANTITY1>", 5, null);
            Replace(worksheet, "<QANTITY2>", 10, null);


            // Delete output file if exists
            if (File.Exists("output.xls"))
            {
                File.Delete("output.xls");
            }

            // Save document
            document.SaveAs("output.xls");

            // Close spreadsheet
            document.Close();

            // Open generated XLS document in default application
            Process.Start("output.xls");
        }
Esempio n. 30
0
        static void Main(string[] args)
        {
            // Open spreadsheet from file
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("example.xls");

            // Get first worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Select cell range
            Range range = worksheet.Range("A2:B8");

            // Copy range to C10 cell
            range.CopyInto("C10");

            // Delete output file if exists
            if (File.Exists("changed.xls"))
            {
                File.Delete("changed.xls");
            }

            // Save document
            document.SaveAs("changed.xls");

            // Close spreadsheet
            document.Close();

            // Open generated XLS document in default program
            Process.Start("changed.xls");
        }
        static void Main(string[] args)
        {
            // Open Spreadsheet
            Spreadsheet document = new Spreadsheet();
	document.LoadFromFile("AdvancedReport.xls");

            // Get Worksheet
            Worksheet worksheet = document.Workbook.Worksheets[0];

            // Add new row
            worksheet.Rows.Insert(6, 1);
	    // Set values
            worksheet.Rows[6][0].Value = "New Name";
            worksheet.Rows[6][1].Value = "New Full Name";

            // delete output file if exists already
            if (File.Exists("AdvancedReport_Modified.xls")){
                File.Delete("AdvancedReport_Modified.xls");
            }


            // Save document
            document.SaveAs("AdvancedReport_Modified.xls");

            // Close Spreadsheet
            document.Close();

            // open in default spreadsheets viewer/editor
            Process.Start("AdvancedReport_Modified.xls");


        }
Esempio n. 32
0
        static void Main(string[] args)
        {
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            document.LoadFromFile("Data.xls");

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");

            // Set new worksheet name
            worksheet.Name = "Collected Data Sheet";

            // delete output file if exists already
            if (File.Exists("Output.xls"))
            {
                File.Delete("Output.xls");
            }

            // Save document
            document.SaveAs("Output.xls");

            // Close Spreadsheet
            document.Close();

            // open generated XLS document in default program
            Process.Start("Output.xls");
        }
        public ClientsInfoSqLiteExporter Export()
        {
            var clientsInfo = this.sqliteData.GetAll();

            Spreadsheet document = new Spreadsheet();

            // add new worksheet
            Worksheet sheet = document.Workbook.Worksheets.Add("Client contacts");

            // headers to indicate purpose of the column
            sheet.Cell("A1").Value = "Company Name";
            sheet.Cell("B1").Value = "Email";
            sheet.Cell("C1").Value = "Address";
            sheet.Cell("D1").Value = "Iban";

            Color headerColor = Color.FromArgb(80, 80, 80);
            sheet.Rows[0].Height = 35;

            for (int i = 0; i < 4; i++)
            {
                sheet.Cell(0, i).FillPattern = PatternStyle.Solid;
                sheet.Cell(0, i).FillPatternForeColor = headerColor;
                sheet.Cell(0, i).FontColor = Color.White;
                sheet.Cell(0, i).Font = new Font("Arial", 14, FontStyle.Bold);
                sheet.Cell(0,i).AlignmentHorizontal = AlignmentHorizontal.Centered;
                sheet.Cell(0,i).AlignmentVertical = AlignmentVertical.Centered;
                sheet.Columns[i].Width = 250;
            }

            // delete output file if exists already
            if (File.Exists(documentPath))
            {
                File.Delete(documentPath);
            }

            var row = 1;
            foreach (var entity in clientsInfo)
            {
                sheet.Cell(row, 0).Value = entity.Name;
                sheet.Cell(row, 1).Value = entity.Email;
                sheet.Cell(row, 2).Value = entity.Address;
                sheet.Cell(row, 3).Value = entity.Iban;
                row++;
            }

            document.SaveAs(documentPath);

            // Close Spreadsheet
            document.Close();
            return this;
        }
Esempio n. 34
0
        public void Seed(string townName, DateTime date, string rootFolder, IEnumerable<SalesInfo> products)
        {
            Spreadsheet document = new Spreadsheet();
            Worksheet sheet = document.Workbook.Worksheets.Add("SalesReport");

            sheet.Cell(0, 0).Value = "Product";
            sheet.Columns[0].Width = 250;

            sheet.Cell(0, 1).Value = "Price";
            sheet.Columns[1].Width = 250;

            sheet.Cell(0, 2).Value = "Quantity";
            sheet.Columns[2].Width = 250;

            sheet.Cell(0, 3).Value = "Sum";
            sheet.Columns[3].Width = 250;

            var rowNumber = 1;

            foreach (var product in products)
            {
                sheet.Cell(rowNumber, 0).Value = product.ProductId;
                sheet.Cell(rowNumber, 1).Value = product.Price;
                sheet.Cell(rowNumber, 2).Value = product.Quantity;
                sheet.Cell(rowNumber, 3).Value = product.Sum;

                ++rowNumber;
            }

            sheet.Range(rowNumber, 0, rowNumber, 3).Merge();
            sheet.Range(rowNumber, 0, rowNumber, 3).Value = products.Sum(x => x.Sum);

            var folderName = rootFolder + date.ToString("yyyy MMM dd");
            if (!Directory.Exists(folderName))
            {
                Directory.CreateDirectory(folderName);
            }

            var fileName = townName + ".xls";
            var filePath = folderName + "/" + fileName;

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            document.SaveAs(filePath);

            document.Close();
        }
Esempio n. 35
0
        public static List<XlsFileStructure> Reader(string filePath)
        {
            Spreadsheet document = new Spreadsheet();
            document.LoadFromFile(filePath);
            Worksheet worksheet = document.Workbook.Worksheets.ByName("Template");

            var fileData = new List<XlsFileStructure>();

            for (var col = 0; col < worksheet.UsedRangeColumnMax; col++)
            {
                fileData.Add(new XlsFileStructure
                {
                    Name = worksheet.Cell(1, col).ValueAsString,
                    Value = worksheet.Cell(2, col).ValueAsString
                });
            }

            document.Close();
            return fileData;
        }
        public void Generate(CombinationsContext combinationsContext, MySqlContext mySqlContext)
        {
            Spreadsheet document = new Spreadsheet();
            Worksheet sheet = document.Workbook.Worksheets.Add("SalesReport");
            sheet.Cell(0, 0).Value = "TOWN";
            sheet.Cell(0, 1).Value = "STORE";
            sheet.Cell(0, 2).Value = "DATE";
            sheet.Cell(0, 3).Value = "SOLD LAPTOPS COUNT";

            Color headerColor = Color.FromArgb(75, 172, 198);
            sheet.Cell(0, 0).FillPattern = PatternStyle.Solid;
            sheet.Cell(0, 1).FillPattern = PatternStyle.Solid;
            sheet.Cell(0, 2).FillPattern = PatternStyle.Solid;
            sheet.Cell(0, 3).FillPattern = PatternStyle.Solid;
            sheet.Cell(0, 0).FillPatternForeColor = headerColor;
            sheet.Cell(0, 1).FillPatternForeColor = headerColor;
            sheet.Cell(0, 2).FillPatternForeColor = headerColor;
            sheet.Cell(0, 3).FillPatternForeColor = headerColor;

            sheet.Columns[0].Width = 300;
            sheet.Columns[1].Width = 300;
            sheet.Columns[2].Width = 300;
            sheet.Columns[3].Width = 300;

            var a = mySqlContext.GetAll<SalesReport>().ToList();
            var row = 1;
            var bs = combinationsContext.Stores.ToList();

            foreach (var report in a)
            {
                sheet.Cell(row, 0).Value = report.Town;
                sheet.Cell(row, 1).Value = bs.First(x => x.Town == report.Town).Store;
                sheet.Cell(row, 2).Value = report.Quantity;
                sheet.Cell(row, 3).Value = report.Date;
                ++row;
            }

            document.SaveAs("export.xls");

            document.Close();
        }
Esempio n. 37
0
        public static string GetMenuExcelFile(this IRepositoryAsync<MenuForWeek> repository, ForMenuExcelDto dto)
        {
            WeekMenuDto weekMenuDto = repository.MapWeekMenuDto(dto.WeekYear);
            WorkingWeek wweek = repository.WorkWeekByWeekYear(dto.WeekYear);
            string[] daynames = wweek.WorkingDays.Where(wd => wd.IsWorking).OrderBy(wd=>wd.DayOfWeek.Id).Select(wd => wd.DayOfWeek.Name).ToArray();
            string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
            int daycount = weekMenuDto.WorkWeekDays.Count(d => d);
            int catLength = dishCategories.Length;
            List<MenuForDayDto> mfdays = new List<MenuForDayDto>();
            for (int i = 0; i < weekMenuDto.WorkWeekDays.Length; i++)
            {
                if (weekMenuDto.WorkWeekDays[i])
                {
                    mfdays.Add(weekMenuDto.MfdModels[i]);
                }
            }
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets.Add("Меню");

            string endcolname = GetExcelColumnName(4);

            string allstr = string.Format("A{0}:D{1}", 1, mfdays.Count*(dishCategories.Length + 1) + 3);

            string titlerang = String.Format("A1:{0}1", GetExcelColumnName(4));
            Range range = worksheet.Range(titlerang);
            range.Merge();
            worksheet.Cell("A1").MergedWithCell.Value = "Меню " + dto.MenuTitle;
            range.AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Columns[0].Width = 180;
            worksheet.Cell(1, 1).Value = "Наименование блюд";
            worksheet.Columns[1].Width = 450;
            worksheet.Columns[2].Width = 90;
            worksheet.Columns[3].Width = 120;
            worksheet.Rows[0].Height = 50;
            worksheet.Rows[0].AlignmentVertical=AlignmentVertical.Centered;
            worksheet.Rows[1].Height = 50;
            worksheet.Rows[1].AlignmentVertical = AlignmentVertical.Centered;
            worksheet.Range("A2:D2").FillPattern = PatternStyle.Solid;
            worksheet.Range("A2:D2").FillPatternForeColor = Color.FromArgb(48, 127, 217);
            worksheet.Range("C2:D2").Merge();
            worksheet.Cell(1, 2).MergedWithCell.Value = "Цена, грн";
            worksheet.Range("A2:D2").AlignmentHorizontal = AlignmentHorizontal.Centered;
            for (int i = 0; i < daycount; i++)
            {
                MenuForDayDto mfd = mfdays[i];
                int strcount = i*catLength + 2;
                string colname = string.Format("A{0}:D{1}", strcount + 1 + i, strcount + 1 + i);
                worksheet.Range(colname).Merge();
                worksheet.Cell(strcount + i, 0).MergedWithCell.Value = daynames[i];
                worksheet.Range(colname).AlignmentHorizontal = AlignmentHorizontal.Centered;
                worksheet.Range(colname).FillPattern = PatternStyle.Solid;
                worksheet.Range(colname).FillPatternForeColor = Color.FromArgb(144, 164, 187);
                for (int j = 0; j < mfd.Dishes.Count; j++)
                {
                    worksheet.Cell(strcount + j + 1 + i, 0).Value = mfd.Dishes[j].Category;
                    worksheet.Cell(strcount + j + 1 + i, 0).Indent = 2;
                    worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title;
                    worksheet.Cell(strcount + j + 1 + i, 1).Indent = 2;
                    bool deskexists = !string.IsNullOrEmpty(mfd.Dishes[j].Description);
                    if (deskexists)
                    {
                        worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title + ":" +
                                                                        mfd.Dishes[j].Description;
                        worksheet.Cell(strcount + j + 1 + i, 1).Font = new Font("Arial", 12, FontStyle.Bold);
                    }
                    worksheet.Cell(strcount + j + 1 + i, 1).Wrap = true;
                    worksheet.Cell(strcount + j + 1 + i, 2).Value = mfd.Dishes[j].Price;
                    worksheet.Cell(strcount + j + 1 + i, 2).NumberFormatString = "#,##0.00";
                    worksheet.Rows[strcount + j + 1 + i].Height = (uint) (!deskexists ? 70 : 90);
                    worksheet.Rows[strcount + j + 1 + i].AlignmentVertical = AlignmentVertical.Centered;
                }
                string sumdaytotal = string.Format("D{0}:D{1}", strcount + 2 + i, strcount + 1 + i + catLength);
                worksheet.Range(sumdaytotal).Merge();
                worksheet.Range(sumdaytotal).AlignmentHorizontal = AlignmentHorizontal.Centered;
                worksheet.Range(sumdaytotal).AlignmentVertical = AlignmentVertical.Centered;
                worksheet.Cell(strcount + 1 + i, 3).Value = mfd.TotalPrice;
                worksheet.Range(sumdaytotal).NumberFormatString = "#,##0.00";
            }
            string totalstr = string.Format("A{0}:C{0}", daycount*(catLength+1) + 3);
            worksheet.Range(totalstr).Merge();
            worksheet.Cell(daycount*(catLength + 1) + 2, 0).Value = "Всего ";
            worksheet.Range(totalstr).AlignmentHorizontal = AlignmentHorizontal.Right;
            worksheet.Cell(daycount * (catLength + 1) + 2, 3).Value = weekMenuDto.SummaryPrice;
            worksheet.Cell(daycount * (catLength + 1) + 2, 3).AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Cell(daycount * (catLength + 1) + 2, 3).NumberFormatString = "#,##0.00";

            worksheet.Rows[daycount * (catLength + 1) + 2].Height = 60;
            worksheet.Rows[daycount * (catLength + 1) + 2].AlignmentVertical = AlignmentVertical.Centered;
            worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
            worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
            worksheet.Range(allstr).Font = new Font("Arial", 14, FontStyle.Bold);

            //string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
            //               @"ACSDining.Web\ExcelFiles\Menu.xls";

            string pathstr = string.Format("~/ExcelFiles/Меню_{0}.xls", YearWeekHelp.GetWeekTitle(repository, dto.WeekYear));
            string _path = HostingEnvironment.MapPath(pathstr);
            if (File.Exists(_path))
            {
                File.Delete(_path);
            }
            try
            {
                document.SaveAs(_path);
            }
            catch (Exception)
            {

                throw;
            }

            // Close document
            document.Close();

            return _path;
        }
Esempio n. 38
0
        public static string GetPlanOrdersExcelFileWeekYearDto(this IRepositoryAsync<WeekOrderMenu> repository,
            ForExcelDataDto feDto)
        {
            string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
            List<PlannedWeekOrderMenu> weekOrderMenus =
                repository.GetRepositoryAsync<PlannedWeekOrderMenu>().OrdersMenuByWeekYear(feDto.WeekYear);
            List<PlanUserWeekOrderDto> userWeekOrders =
                weekOrderMenus.Select(woDto => PlanUserWeekOrderDto.MapDto(repository.Context, woDto)).ToList();
            string[] dayNames = repository.Context.GetDayNames(feDto.WeekYear, true).Result;
            double[] weekDishPrices = repository.Context.GetWeekDishPrices(feDto.WeekYear).Result;
            double[] summaryDishQuantities = repository.Context.GetFactSumWeekUserCounts(feDto.WeekYear).Result;

            WorkingWeek workWeek = repository.GetRepositoryAsync<MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear);
            int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking);
            int catLength = repository.GetRepositoryAsync<DishType>().GetAll().Count;

            int dishcount = workDayCount*catLength;
            int orderscount = userWeekOrders.Count;
            // Create new Spreadsheet
            Spreadsheet document = new Spreadsheet();

            // Get worksheet by name
            Worksheet worksheet = document.Workbook.Worksheets.Add("Заявки плановые");

            string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 3));
            Range range = worksheet.Range(titlerang);
            range.Merge();
            worksheet.Cell("A1").MergedWithCell.Value = "Заявки плановые " + feDto.DataString;
            range.AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Cell(2, 0).Value = "№";
            worksheet.Range("A2:A5").Merge();
            worksheet.Cell(2, 1).Value = "Ф.И.О.";
            worksheet.Range("B2:B5").Merge();
            worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered;

            string str;
            string colname;
            string colname_2;
            string endcolname = GetExcelColumnName(dishcount + 3);
            string allstr = string.Format("A{0}:{2}{1}", 1, userWeekOrders.Count + 6, endcolname);

            colname = GetExcelColumnName(dishcount + 2);
            worksheet.Cell(2 + 1, 3).Value = "Цена за одну порцию, грн";
            str = String.Format("C4:{0}4", colname);
            worksheet.Range(str).Merge();
            worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;

            int i = dishcount + 3;
            colname = GetExcelColumnName(i);
            worksheet.Cell(1, i - 1).Value = "Стоимость заказа за неделю";
            str = String.Format("{0}2:{1}5", colname, colname);
            worksheet.Range(str).Merge();
            worksheet.Range(str).Wrap = true;
            worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Columns[i - 1].Width = 100;
            worksheet.Cell(1, i - 1).ShrinkToFit = true;

            i = userWeekOrders.Count + 5;
            worksheet.Cell(i, 0).Value = "Всего заказано";
            str = String.Format("A{0}:B{1}", i + 1, i + 1);
            worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Right;
            worksheet.Range(str).Merge();

            worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
            worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;

            for (int j = 0; j < dishcount; j++)
            {
                worksheet.Cell(4, 2 + j).Value = weekDishPrices[j];
                if ((j) % 4 != 0)
                {
                    worksheet.Cell(4, 2 + j).LeftBorderStyle = LineStyle.Thin;
                }
                worksheet.Cell(4, 2 + j).RightBorderStyle = LineStyle.Thin;
            }

            i = 0;
            for (int[] j = { 0 }; j[0] < workDayCount; j[0]++)
            {
                colname = GetExcelColumnName(j[0] * catLength + 3);
                colname_2 = GetExcelColumnName(j[0] * catLength + 6);
                var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]);
                if (elementAtOrDefault != null)
                    worksheet.Cell(1, j[0] * catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name;
                str = String.Format("{0}2:{1}2", colname, colname_2);
                worksheet.Range(str).Merge();
            }

            i = 2;
            for (int j = 0; j < workDayCount; j++)
            {
                for (int k = 0; k < catLength; k++)
                {
                    colname = GetExcelColumnName(2 + 1 + j * catLength + k);
                    worksheet.Cell(2, 2 + j * catLength + k).Value = dishCategories[k];
                    worksheet.Range(colname + "3").Rotation = 90;
                    if ((k) % 4 != 0)
                    {
                        worksheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin;
                    }
                    worksheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin;
                }
            }
            str = string.Format("A1:{0}5", GetExcelColumnName(dishcount + 3));
            i = 5;
            Color contentColor = Color.FromArgb(224, 232, 241);
            Color nullColor = Color.FromArgb(6, 84, 156);
            for (int j = 0; j < userWeekOrders.Count; j++)
            {
                var itsevenrow = (i + j) % 2 != 0;
                PlanUserWeekOrderDto userweekorder = userWeekOrders[j];
                worksheet.Cell(i + j, 0).Value = j + 1;
                worksheet.Cell(i + j, 1).Value = userweekorder.UserName;
                if (itsevenrow)
                {
                    worksheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid;
                    worksheet.Cell(i + j, 0).FillPatternForeColor = contentColor;
                    worksheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid;
                    worksheet.Cell(i + j, 1).FillPatternForeColor = contentColor;
                }
                worksheet.Cell(i + j, 1).ShrinkToFit = true;
                for (int k = 0; k < dishcount + 1; k++)
                {
                    var celval = userweekorder.UserWeekOrderDishes[k];
                    if (celval != 0.00)
                    {
                        worksheet.Cell(i + j, k + 2).Value = celval;
                    }
                    if (itsevenrow)
                    {
                        worksheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid;
                        worksheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor;
                    }
                    if ((k) % 4 != 0)
                    {
                        worksheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin;
                    }
                    worksheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin;
                }

            }
            i = userWeekOrders.Count + 5;
            Color evcolor = Color.FromArgb(68, 240, 196);
            for (int j = 0; j < workDayCount; j++)
            {
                for (int k = 0; k < catLength; k++)
                {
                    Cell curCell = worksheet.Cell(i, j * catLength + k + 2);
                    curCell.Value = summaryDishQuantities[j * catLength + k];
                    if (j % 2 == 0)
                    {
                        curCell.FillPattern = PatternStyle.Solid;
                        curCell.FillPatternForeColor = evcolor;
                    }
                    if ((k) % 4 != 0)
                    {
                        worksheet.Cell(i, j * catLength + k + 2).LeftBorderStyle = LineStyle.Thin;
                    }
                    worksheet.Cell(i, j * catLength + k + 2).RightBorderStyle = LineStyle.Thin;
                }
            }
            worksheet.Cell(i, dishcount + 2).Value = userWeekOrders.Sum(uo => uo.UserWeekOrderDishes[dishcount]);

            string headerstr = string.Format("C{0}:{2}{1}", 1, 3, endcolname);
            worksheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
            string headerusnamesstr = string.Format("A{0}:B{1}", 1, 5);
            worksheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered;
            string usernames = string.Format("A{0}:B{1}", 6, userWeekOrders.Count + 5);
            worksheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left;
            string userquantistr = string.Format("C{0}:{2}{1}", 5, userWeekOrders.Count + 6, endcolname);
            //worksheet.Range(userquantistr).NumberFormatString = "#.#";
            worksheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered;
            string sumcol = string.Format("{0}{1}:{2}{3}", endcolname, 5, endcolname, userWeekOrders.Count + 6);
            worksheet.Range(sumcol).NumberFormatString = "#,##0.00";
            worksheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered;
            worksheet.Columns[0].Width = 40;
            worksheet.Columns[1].Width = 250;
            //worksheet.Columns[1].AutoFit();
            worksheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold);

            for (int j = 0; j < userWeekOrders.Count + 6; j++)
            {
                worksheet.Rows[j].Height = (uint)((j != 2) ? 35 : 150);
                worksheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered;
            }

            //string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
            //               @"ACSDining.Web\ExcelFiles\ЗаявкиПлан.xls";
            string pathstr = string.Format("~/ExcelFiles/ЗаявкиПлан_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync<MenuForWeek>(), feDto.WeekYear));
            string _path = HostingEnvironment.MapPath(pathstr);
            if (File.Exists(_path))
            {
                File.Delete(_path);
            }
            document.SaveAs(_path);

            // Close document
            document.Close();

            return _path;
        }
Esempio n. 39
0
        public static string GetExcelFileFromPaimentsModel(this IRepositoryAsync<WeekOrderMenu> repository,
            ForExcelDataDto feDto)
        {
            WeekPaimentDto dto = WeekPaimentDto.GetMapDto(repository.GetRepositoryAsync<WeekPaiment>(), feDto.WeekYear);
            string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
            WorkingWeek workWeek = repository.GetRepositoryAsync<MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear);
            int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking);
            int catLength = repository.GetRepositoryAsync<DishType>().GetAll().Count;
            List<UserWeekPaimentDto> paimentList = dto.UserWeekPaiments;
            //Цены за  каждое блюдо в меню на рабочей неделе
            double[] unitPrices = dto.WeekDishPrices;
            int dishcount = workDayCount*catLength;
            //Выделяем память для искомых данных ( +1 для хранения суммы всех ожидаемых проплат)
            double[] unitPricesTotal =
                new double[dishcount + 1];

            for (int i = 0; i < dishcount; i++)
            {
                unitPricesTotal[i] = dto.SummaryDishPaiments[i];
            }

            unitPricesTotal[dishcount] = dto.SummaryDishPaiments.Sum();

            Spreadsheet document = new Spreadsheet();

            //document.Workbook.Worksheets.DeleteAll();
            // Get worksheet by name
            Worksheet workSheet = document.Workbook.Worksheets.Add("Оплаты");
            // I created Application and Worksheet objects before try/catch,
            // so that i can close them in finnaly block.
            // It's IMPORTANT to release these COM objects!!
            try
            {
                // ------------------------------------------------
                // Creation of header cells
                // ------------------------------------------------
                if (workSheet != null)
                {

                    string endcolname = GetExcelColumnName(dishcount + 6);
                    string allstr = string.Format("A1:{0}{1}", endcolname, paimentList.Count + 6);
                    workSheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold);

                    workSheet.Cell(1, 0).Value = "№";
                    workSheet.Range("A2:A5").Merge();

                    workSheet.Range("B2:B5").Merge();
                    workSheet.Cell(1, 1).MergedWithCell.Value = "Ф.И.О.";
                    workSheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered;

                    string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 6));
                    Range range = workSheet.Range(titlerang);
                    range.Merge();
                    workSheet.Cell("A1").MergedWithCell.Value = "Оплаты на " + feDto.DataString;
                    range.AlignmentHorizontal = AlignmentHorizontal.Centered;
                    int i = 0;
                    string str;
                    string colname;
                    string colname_2;
                    for (int[] j = {0}; j[0] < workDayCount; j[0]++)
                    {
                        colname = GetExcelColumnName(j[0]*catLength + 3);
                        colname_2 = GetExcelColumnName(j[0]*catLength + 6);
                        var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]);
                        if (elementAtOrDefault != null)
                            workSheet.Cell(1, j[0]*catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name;
                        str = String.Format("{0}2:{1}2", colname, colname_2);
                        workSheet.Range(str).Merge();
                    }
                    i += dishcount + 2;
                    colname = GetExcelColumnName(i + 1);
                    str = String.Format("{0}2:{1}5", colname, colname);
                    workSheet.Range(str).Merge();
                    workSheet.Cell(2, i).MergedWithCell.Value = "Сумма к оплате ";
                    workSheet.Range(str).Rotation = 90;
                    workSheet.Columns[i].Width = 90;
                    i++;
                    colname = GetExcelColumnName(i + 1);
                    workSheet.Cell(2, i).Value = "Оплата за неделю";
                    str = String.Format("{0}2:{1}5", colname, colname);
                    workSheet.Range(str).Merge();
                    workSheet.Range(str).Rotation = 90;
                    workSheet.Columns[i].Width = 90;
                    i++;
                    colname = GetExcelColumnName(i + 1);
                    workSheet.Cell(2, i).Value = "Баланс";
                    str = String.Format("{0}2:{1}5", colname, colname);
                    workSheet.Range(str).Merge();
                    workSheet.Range(str).Rotation = 90;
                    workSheet.Columns[i].Width = 80;
                    i++;
                    colname = GetExcelColumnName(i + 1);
                    workSheet.Cell(2, i).Value = "Примечание";
                    str = String.Format("{0}2:{1}5", colname, colname);
                    workSheet.Range(str).Merge();
                    workSheet.Range(str).Rotation = 90;
                    workSheet.Columns[i].Width = 90;

                    colname = GetExcelColumnName(dishcount + 2);
                    workSheet.Cell(3, 2).Value = "Цена за одну порцию, грн";
                    str = String.Format("C4:{0}4", colname);
                    workSheet.Range(str).Merge();
                    workSheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered;

                    i = paimentList.Count + 5;
                    str = String.Format("A{0}:B{1}", i + 1, i + 1);
                    workSheet.Range(str).Merge();
                    workSheet.Cell(i, 0).MergedWithCell.Value = "Итого";
                    workSheet.Cell(i, 0).AlignmentHorizontal = AlignmentHorizontal.Right;

                    workSheet.Columns[i].Width = 120;

                    workSheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
                    workSheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;

                    i = 2;
                    for (int j = 0; j < workDayCount; j++)
                    {
                        for (int k = 0; k < catLength; k++)
                        {
                            colname = GetExcelColumnName(3 + j*catLength + k);
                            workSheet.Cell(2, 2 + j*catLength + k).Value = dishCategories[k];
                            workSheet.Range(colname + "3").Rotation = 90;
                            if ((k) % 4 != 0)
                            {
                                workSheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin;
                            }
                            workSheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin;
                        }
                    }

                    double[] dishprices = unitPrices;
                    for (int j = 0; j < dishcount; j++)
                    {
                        colname = GetExcelColumnName(i + j);
                        workSheet.Cell(4, i + j).Value = dishprices[j];
                        if ((j) % 4 != 0)
                        {
                            workSheet.Cell(4, j + 2).LeftBorderStyle = LineStyle.Thin;
                        }
                        workSheet.Cell(4, j + 2).RightBorderStyle = LineStyle.Thin;
                    }

                    i = paimentList.Count + 5;

                    for (int j = 0; j < dishcount; j++)
                    {

                        colname = GetExcelColumnName(j + 3);
                        workSheet.Cell(i, j + 2).Value = unitPricesTotal[j];
                        if ((j) % 4 != 0)
                        {
                            workSheet.Cell(i, j + 2).LeftBorderStyle = LineStyle.Thin;
                        }
                        workSheet.Cell(i, j + 2).RightBorderStyle = LineStyle.Thin;
                    }
                    colname = GetExcelColumnName(dishcount + 3);
                    workSheet.Cell(i, dishcount + 2).Value = unitPricesTotal[dishcount];
                    colname = GetExcelColumnName(dishcount + 4);
                    workSheet.Cell(i, dishcount + 3).Value = paimentList.Sum(up => up.Paiment);
                    colname = GetExcelColumnName(dishcount + 5);
                    workSheet.Cell(i, dishcount + 4).Value = paimentList.Sum(up => up.Balance);

                    i = 5;
                    Color contentColor = Color.FromArgb(227, 238, 245);
                    Color nullColor = Color.FromArgb(6, 84, 156);
                    for (int j = 0; j < paimentList.Count; j++)
                    {
                        bool ev = (i + j)%2 != 0;
                        UserWeekPaimentDto userpai = paimentList[j];
                        workSheet.Cell(i + j, 0).Value = j + 1;
                        workSheet.Cell(i + j, 1).Value = userpai.UserName;
                        workSheet.Cell(i + j, 1).ShrinkToFit = true;
                        if (ev)
                        {
                            workSheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, 0).FillPatternForeColor = contentColor;
                            workSheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, 1).FillPatternForeColor = contentColor;

                        }
                        for (int k = 0; k < dishcount; k++)
                        {

                            colname = GetExcelColumnName(k + 2);
                            bool itsnulval = userpai.WeekPaiments[k] == 0.00;
                            if (!itsnulval)
                            {
                                workSheet.Cell(i + j, k + 2).Value = userpai.WeekPaiments[k];
                            }
                            if (ev)
                            {
                                workSheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid;
                                workSheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor;

                            }
                            if ((k) % 4 != 0 )
                            {
                                workSheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin;
                            }
                            workSheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin;
                        }

                        colname = GetExcelColumnName(dishcount + 2);
                        workSheet.Cell(i + j, dishcount + 2).Value = paimentList[j].WeekPaiments[workDayCount*catLength];
                        if (ev)
                        {
                            workSheet.Cell(i + j, dishcount + 2).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, dishcount + 2).FillPatternForeColor = contentColor;

                        }
                        colname = GetExcelColumnName(dishcount + 3);
                        if (ev)
                        {
                            workSheet.Cell(i + j, dishcount + 3).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, dishcount + 3).FillPatternForeColor = contentColor;

                        }
                        colname = GetExcelColumnName(dishcount + 4);
                        workSheet.Cell(i + j, dishcount + 4).Value = paimentList[j].Balance;
                        if (ev)
                        {
                            workSheet.Cell(i + j, dishcount + 4).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, dishcount + 4).FillPatternForeColor = contentColor;

                        }
                        colname = GetExcelColumnName(dishcount + 5);
                        workSheet.Cell(i + j, dishcount + 5).Value = paimentList[j].Note;
                        if (ev)
                        {
                            workSheet.Cell(i + j, dishcount + 5).FillPattern = PatternStyle.Solid;
                            workSheet.Cell(i + j, dishcount + 5).FillPatternForeColor = contentColor;
                        }
                        workSheet.Cell(i + j, dishcount + 5).TopBorderStyle = LineStyle.Medium;
                        workSheet.Cell(i + j, dishcount + 5).RightBorderStyle = LineStyle.Medium;
                        workSheet.Cell(i + j, dishcount + 5).BottomBorderStyle = LineStyle.Medium;
                    }
                    colname = GetExcelColumnName(dishcount + 1);
                    string headerstr = string.Format("C{0}:{2}{1}", 1, 2, colname);
                    workSheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
                    string headerusnamesstr = string.Format("A{0}:B{1}", 2, 5);
                    workSheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered;
                    workSheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered;
                    string usernames = string.Format("B{0}:B{1}", 6, paimentList.Count + 5);
                    workSheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left;
                    string userquantistr = string.Format("C{0}:{2}{1}", 5, paimentList.Count + 6, endcolname);
                    //workSheet.Range(userquantistr).NumberFormatString = "0.0";
                    workSheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered;
                    string sumnotestart = GetExcelColumnName(dishcount + 2);
                    string sumnotend = GetExcelColumnName(dishcount + 5);
                    string sumcol = string.Format("{0}{1}:{2}{3}", sumnotestart, 5, sumnotend, paimentList.Count + 7);
                    workSheet.Range(sumcol).NumberFormatString = "#,##0.00";
                    workSheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered;
                    workSheet.Columns[0].Width = 40;
                    workSheet.Columns[1].Width = 250;
                    //worksheet.Columns[1].AutoFit();

                    for (int j = 0; j < paimentList.Count + 6; j++)
                    {
                        workSheet.Rows[j].Height = (uint)((j != 2) ? 35 : 120);
                        workSheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered;
                    }

                    //string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
                    //               @"ACSDining.Web\ExcelFiles\Оплаты.xls";
                    string pathstr = string.Format("~/ExcelFiles/Оплаты_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync<MenuForWeek>(), dto.WeekYearDto));
                    string _path = HostingEnvironment.MapPath(pathstr);
                    // delete output file if exists already
                    if (File.Exists(_path))
                    {
                        File.Delete(_path);
                    }
                    document.SaveAs(_path);

                    // Close document
                    document.Close();

                    return _path;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return null;
        }