Exemple #1
0
        private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
        {
            workSheet.Cells[2, 2].Value = "Date";
            workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();

            workSheet.Cells[2, 3].Value = "Columns1";
            workSheet.Cells[3, 3].Value = 25;
            workSheet.Cells[4, 3].Value = 33;
            workSheet.Cells[5, 3].Value = 30;
            workSheet.Cells[6, 3].Value = 22;

            workSheet.Cells[2, 4].Value = "Column2";
            workSheet.Cells[3, 4].Value = 25;
            workSheet.Cells[4, 4].Value = 33;
            workSheet.Cells[5, 4].Value = 30;
            workSheet.Cells[6, 4].Value = 22;

            workSheet.Cells[2, 5].Value = "Column3";
            workSheet.Cells[3, 5].Value = 25;
            workSheet.Cells[4, 5].Value = 33;
            workSheet.Cells[5, 5].Value = 30;
            workSheet.Cells[6, 5].Value = 22;

            return workSheet.Range("$B2:$E6");
        }
        public void AddDatasetIntoWorksheet(string Title,string workSheetName, DataSet ds, Excel.Worksheet workSheet, Excel.Workbook workbook)
        {
            // font action
            workSheet.Name = workSheetName;

            workSheet.Range("A2").Value = Title;
            workSheet.Range("A2").Font.Name = "Arial";
            workSheet.Range("A2").Font.Size = 10;
            workSheet.Range("A2").Font.Bold = true;
            workSheet.Range("A2").Font.Italic = true;
            workSheet.Range("A2").Font.Underline = true;

            // setup rows and columns
            workSheet.Columns[1].AutoFit();
            workSheet.Columns[2].ColumnWidth = 25;
            workSheet.Columns[3].ColumnWidth = 25;

            workSheet.Columns[4].ColumnWidth = 25;
            workSheet.Columns[5].ColumnWidth = 25;
            CultureInfo cultureInfo = NetOffice.Settings.ThreadCulture;

            List<string> colNamesToHighlight = GetColNamesToHighlight();
            List<string> colNamesToHighlight_Red = GetColNamesToHighlight_Red();

            int RowToStartHighlight = 0;
            int TotalsRow = 0;
            decimal TotalOfInactiveOver90 = 0;
            decimal TotalOfInactiveOver60 = 0;
            List<string> alpha = AToZ();
            int rowNum = 5;
            int colcount = 0;

            //set certain columns to text format
            workSheet.Columns[3].NumberFormat = "@";
            workSheet.Columns[14].NumberFormat = "@";
            workSheet.Columns[17].NumberFormat = "@";
            workSheet.Columns[18].NumberFormat = "@";

            try
            {

                foreach (DataTable table in ds.Tables)
                {
                    //write column headings

                    foreach (DataRow row in table.Rows)
                    {
                        foreach (DataColumn column in table.Columns)
                        {

                            workSheet.Range(alpha[colcount] + rowNum.ToString()).Value = column.ColumnName;
                            workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Bold = true;
                            colcount += 1;
                        }
                        break;
                    }

                    colcount = 0;
                    rowNum = 6;
                    foreach (DataRow row in table.Rows)
                    {
                        foreach (DataColumn column in table.Columns)
                        {

                            workSheet.Range(alpha[colcount] + rowNum.ToString()).Value = row[column];
                            string Pattern2 = string.Format("#{1}##0{0}00",
                            cultureInfo.NumberFormat.CurrencyDecimalSeparator,
                            cultureInfo.NumberFormat.CurrencyGroupSeparator);
                            workSheet.Range(alpha[colcount] + rowNum.ToString()).NumberFormat = Pattern2;

                            if (row[column].ToString().ToLower() == "inactive>60")
                                RowToStartHighlight = rowNum;

                            if (row[column].ToString().ToLower() == "totals")
                                TotalsRow = rowNum;

                            if (TotalsRow == rowNum)
                            {
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Bold = true;
                            }

                            if (IsColToHighlight(colNamesToHighlight, column.ColumnName) && RowToStartHighlight == rowNum)
                            {
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Color = ToDouble(System.Drawing.Color.Green);
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(198, 239, 206));
                                TotalOfInactiveOver90 += Convert.ToDecimal(workSheet.Range(alpha[colcount] + rowNum.ToString()).Value);
                            }

                            if (IsColToHighlight(colNamesToHighlight_Red, column.ColumnName) && RowToStartHighlight == rowNum)
                            {
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Color = ToDouble(System.Drawing.Color.DarkRed);
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(198, 239, 206));
                                workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(204, 128, 51));
                                TotalOfInactiveOver60 += Convert.ToDecimal(workSheet.Range(alpha[colcount] + rowNum.ToString()).Value);
                            }

                            colcount += 1;
                        }
                        rowNum += 1;
                        colcount = 0;
                    }
                }

                int newR = rowNum + 1;

                workSheet.Columns[1].ColumnWidth = 20;
                workSheet.Columns[2].ColumnWidth = 12;
                workSheet.Columns[3].ColumnWidth = 12;
                workSheet.Columns[4].ColumnWidth = 12;
                workSheet.Columns[5].ColumnWidth = 12;
                workSheet.Columns[6].ColumnWidth = 12;
                workSheet.Columns[7].ColumnWidth = 12;
                workSheet.Columns[8].ColumnWidth = 12;
                workSheet.Columns[9].ColumnWidth = 12;
                workSheet.Columns[10].ColumnWidth = 12;
                workSheet.Columns[11].ColumnWidth = 12;
                workSheet.Columns[12].ColumnWidth = 12;
                workSheet.Columns[13].ColumnWidth = 12;

            }
            catch (Exception ex)
            {
                workSheet.Range("A100").Value = ex.Message;
            }
        }
 private static void AddDataRows(Excel.Worksheet sheet)
 {
     sheet.Name = "Financial report";
     var reportResult = GetData();
     var value = new string[reportResult.Length, 5];
     for (var row = 0; row < reportResult.Length; row++)
     {
         Console.WriteLine("Importing:\n---------------");
         for (int col = 0; col < reportResult[row].Length; col++)
         {
             value[row, col] = reportResult[row][col];
             Console.WriteLine(reportResult[row][col]);
         }
     }
     var range = sheet.Range(sheet.Cells[4, 1],
         sheet.Cells[reportResult.Length + 3, reportResult[1].Length]);
     range.Value = value;
     Console.WriteLine("Ready!!!");
 }