示例#1
0
        public void DataSetToExcel(DataSet dataSet, string excelFileName, bool suppressColumnNames, int?startTable = 0,
                                   int?numTables = null, bool allOnSameSheet = false)
        {
            var hasData  = false;
            var workbook = new WorkbookWithStyles();

            string worksheetName = null;

            var relativeTableNum = 0;
            var relativeRowNum   = 0;
            var iNumTables       = numTables ?? dataSet.Tables.Count;
            var iStartTable      = startTable ?? 0;
            var lastTableNum     = iStartTable + iNumTables;
            //var isFirstTable = true;
            ISheet ws = null;

            //var gotCustomWorksheetName = false;
            for (var tableNum = iStartTable; tableNum < lastTableNum; tableNum++)
            {
                var dataTable = dataSet.Tables[tableNum];
                worksheetName = worksheetName ?? dataTable.TableName ?? string.Format("Sheet{0}", ++relativeTableNum);
                if (!allOnSameSheet)
                {
                    if (dataTable.Rows.Count == 1 && dataTable.Columns.Count == 1) //&& !gotCustomWorksheetName)
                    {
                        worksheetName = dataTable.Rows[0][0].ToString();
                        //gotCustomWorksheetName = true;
                        continue;
                    }
                    ws = workbook.CreateWorkSheet(worksheetName);

                    worksheetName  = null;
                    relativeRowNum = 0;
                }
                if (ws == null)
                {
                    ws = workbook.CreateWorkSheet(worksheetName);
                }
                if (allOnSameSheet)
                {
                    relativeRowNum++;
                }
                IRow row;
                if (!suppressColumnNames)
                {
                    if (ws == null)
                    {
                        throw new Exception("Worksheet is null!!");
                    }
                    row = ws.CreateRow(relativeRowNum++);
                    for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        var cell = row.CreateCell(columnIndex);
                        workbook.SetHeaderValueAndFormat(cell, dataTable.Columns[columnIndex].ColumnName);
                    }
                }

                RelatedColumnPairs relatedColumns = null;
                if (ShowChanges)
                {
                    relatedColumns = FindRelatedColumns(dataTable);
                }

                //var longCols = new List<int>();
                for (var rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                {
                    hasData = true;
                    if (ws == null)
                    {
                        throw new Exception("Worksheet is null!!");
                    }

                    var dataRow = dataTable.Rows[rowIndex];
                    if (ShowChanges)
                    {
                        relatedColumns.DoCompare(dataRow);
                    }

                    row = ws.CreateRow(relativeRowNum++);
                    for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        var colDataType = dataTable.Columns[columnIndex].DataType;
                        var obj         = dataRow[columnIndex];
                        var value       = obj == DBNull.Value ? string.Empty : obj.ToString();

                        var condFormatting = ConditionalStyle.None;
                        if (ShowChanges && relatedColumns.IsDifferent(columnIndex))
                        {
                            condFormatting = ConditionalStyle.Note;
                        }

                        var cell = row.CreateCell(columnIndex);

                        if (colDataType == typeof(decimal) || colDataType == typeof(double))
                        {
                            workbook.SetValueAndFormat(cell, SafeConvertToDouble(value), condFormatting);
                        }
                        else if (colDataType == typeof(DateTime))
                        {
                            workbook.SetValueAndFormat(cell, SafeConvertToDateTime(value), condFormatting);
                        }
                        else if (colDataType == typeof(int))
                        {
                            workbook.SetValueAndFormat(cell, SafeConvertToInt(value), condFormatting);
                        }
                        else
                        {
                            workbook.SetValueAndFormat(cell, value, condFormatting);
                        }
                    }
                }
                if (ShowChanges)
                {
                    ws.CreateFreezePane(0, 1, 0, 1);
                    ws.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1));
                }
                for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    ws.AutoSizeColumn(columnIndex);
                }
            }
            if (!hasData)
            {
                return;
            }
            var tempFile = Path.GetTempFileName();

            using (var fileData = new FileStream(tempFile, FileMode.Create))
            {
                workbook.Write(fileData);
            }
            try
            {
                File.Delete(excelFileName);
                File.Move(tempFile, excelFileName);
            } catch (Exception ex)
            {
                throw new Exception(string.Format("Error moving {0}: {1}", tempFile, ex.Message));
            }
        }
 public CellStyleLib(WorkbookWithStyles workbook)
 {
     _workbook = workbook;
 }