Пример #1
0
        private void InsertDataTableRows(Worksheet worksheet, ExcelDataTable excelDataTable)
        {
            int rowCount      = excelDataTable.RowCount;
            int colCount      = excelDataTable.ColumnCount;
            int startRowIndex = excelDataTable.StartRowIndex;
            int startColIndex = excelDataTable.StartColumnIndex;

            int colIndex;

            for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    string result = excelDataTable.GetCellValueAt(rowIndex - 1, colIndex);
                    double number;
                    if (Double.TryParse(result, out number))
                    {
                        Range numberRange = worksheet.Cells[startRowIndex + rowIndex, startColIndex + colIndex];
                        numberRange.NumberFormat = "0.0000";
                    }
                    worksheet.Cells[startRowIndex + rowIndex, startColIndex + colIndex] = result != "" ?
                                                                                          result : "=NA()";
                }
            }
        }
Пример #2
0
        private void InsertProtocolCommentsDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet,
                                                     ProtocolTemplate title)
        {
            DataTable      dataTable      = QProtocolComments.SelectItemsToDataTable(title);
            ExcelDataTable excelDataTable = CreateNewExcelDataTable("CommentsTable" + title.TemplateID, dataTable, 5, 4);

            InsertExcelDataTable(excelTemplate, worksheet, excelDataTable);
            FormatExcelDataTable(worksheet, excelDataTable);
        }
Пример #3
0
        private void InsertDataTableColumns(Worksheet worksheet, ExcelDataTable excelDataTable)
        {
            int rowIndex = excelDataTable.StartRowIndex;
            int colCount = excelDataTable.ColumnCount;

            for (int i = 0; i < colCount; i++)
            {
                worksheet.Cells[rowIndex, i + excelDataTable.StartColumnIndex] = excelDataTable.GetColumnName(i);
            }
        }
Пример #4
0
        private void InsertProtocolEventsDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet,
                                                   ProtocolTemplate title)
        {
            DataTable dataTable = QProtocolActivities.SelectItemsToDataTable(this.protocolRequest.ID,
                                                                             title.TemplateID);
            ExcelDataTable eventsDataTable = CreateNewExcelDataTable("EventsTable" + title.TemplateID, dataTable, 1, 4);

            InsertExcelDataTable(excelTemplate, worksheet, eventsDataTable);
            FormatExcelDataTable(worksheet, eventsDataTable);
        }
Пример #5
0
        private ExcelDataTable CreateNewExcelDataTable(string tableName, System.Data.DataTable dataTable,
                                                       int startColumnIndex, int startRowIndex)
        {
            ExcelDataTable excelDataTable = new ExcelDataTable();

            excelDataTable.SetDataTable(dataTable);
            excelDataTable.StartColumnIndex = startColumnIndex;
            excelDataTable.StartRowIndex    = startRowIndex;
            excelDataTable.SetTopLeft();
            excelDataTable.SetBottomRight();
            excelDataTable.TableName      = tableName;
            excelDataTable.TableStyleName = "TableStyleLight11";

            return(excelDataTable);
        }
Пример #6
0
        private void SetDataTableRange(Worksheet worksheet, ExcelDataTable excelDataTable)
        {
            Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight);

            worksheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, dataTableRange, Type.Missing,
                                        XlYesNoGuess.xlNo, Type.Missing).Name = excelDataTable.TableName;
            ListObject listObj = worksheet.ListObjects.get_Item(excelDataTable.TableName);

            worksheet.ListObjects.get_Item(excelDataTable.TableName).TableStyle = excelDataTable.TableStyleName;
            var type = listObj.GetType();

            if (type.GetMethod("ShowAutoFilterDropDown") != null)
            {
                worksheet.ListObjects.get_Item(excelDataTable.TableName).ShowAutoFilterDropDown = false;
            }
        }
Пример #7
0
 public void InsertDataTable(Worksheet worksheet, ExcelDataTable excelDataTable)
 {
     SetDataTableRange(worksheet, excelDataTable);
     InsertDataTableColumns(worksheet, excelDataTable);
     InsertDataTableRows(worksheet, excelDataTable);
 }
Пример #8
0
        public void SetDataTableColumnWidth(Worksheet worksheet, ExcelDataTable excelDataTable, int width)
        {
            Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight);

            dataTableRange.ColumnWidth = width;
        }
Пример #9
0
        public static void SetDataTableColumnAutoFit(Worksheet worksheet, ExcelDataTable excelDataTable)
        {
            Range dataTableRange = worksheet.get_Range(excelDataTable.TopLeft + ":" + excelDataTable.BottomRight);

            dataTableRange.EntireColumn.AutoFit();
        }
Пример #10
0
 private void FormatExcelDataTable(ExcelWorksheet worksheet, ExcelDataTable excelDataTable)
 {
     //ExcelTemplate.SetWrapText(worksheet, excelDataTable.TopLeft, excelDataTable.BottomRight);
     ExcelTemplate.SetTopAlignLeft(worksheet, excelDataTable.TopLeft, excelDataTable.BottomRight);
     ExcelTemplate.SetDataTableColumnAutoFit(worksheet, excelDataTable);
 }
Пример #11
0
 private void InsertExcelDataTable(ExcelTemplate excelTemplate, ExcelWorksheet worksheet,
                                   ExcelDataTable excelDataTable)
 {
     excelTemplate.InsertDataTable(worksheet, excelDataTable);
     excelTemplate.SetDataTableColumnWidth(worksheet, excelDataTable, 13);
 }