Esempio n. 1
0
        public static void DataReaderToExcelFile(IDataReader reader, string saveFileName, List<SheetOptions> optionsList)
        {
            if (reader == null) throw new ArgumentNullException("reader");
            if (string.IsNullOrWhiteSpace(saveFileName)) throw new ArgumentException("字符串参数不允许为null或者空值.", saveFileName);
            // 创建一个空Xls文档
            XlsDocument xls = new XlsDocument();

            int sheetIndex = 0;
            int sheetDefaultNameIndex = 1;
            do
            {
                // 创建一个工作表
                SheetOptions options = (optionsList == null || sheetIndex >= optionsList.Count)
                    ? options = new SheetOptions() { SheetName = SheetOptions.DefaultSheetName + (sheetDefaultNameIndex++).ToString() }
                    : optionsList[sheetIndex];
                Worksheet sheet = xls.Workbook.Worksheets.Add(options.SheetName);

                // 创建表格数据

                if (reader.Read())
                {
                    // 标题
                    ushort rowNo = 1,
                        columnNo = 1;
                    int fieldCnt = reader.FieldCount;
                    for (int i = 0; i < fieldCnt; i++, columnNo++)
                    {
                        sheet.Cells.Add(rowNo, columnNo, reader.GetName(i));
                    }

                    // 正文数据
                    do
                    {
                        rowNo++;

                        columnNo = 1;
                        for (int i = 0; i < fieldCnt; i++, columnNo++)
                        {
                            string value = Convert.ToString(reader[i]);  // null值将显示为空
                            sheet.Cells.Add(rowNo, columnNo, value);
                        }
                    } while (reader.Read());
                }

                sheetIndex++;
            } while (reader.NextResult());

            trySaveXlsFile(xls, saveFileName);
        }
Esempio n. 2
0
        public static void DataTableToExcelFile(DataTable dataTable, string saveFileName, SheetOptions options)
        {
            if (dataTable == null) throw new ArgumentNullException("dataTable");
            if (string.IsNullOrWhiteSpace(saveFileName)) throw new ArgumentException("字符串参数不允许为null或者空值.", saveFileName);

            if (options == null)
                options = new SheetOptions();

            ushort rowNo = 0, columnNo = 0;
            int columnNum = dataTable.Columns.Count;

            // 创建一个空Xls文档
            XlsDocument xls = new XlsDocument();

            // 创建一个工作表
            Worksheet sheet = xls.Workbook.Worksheets.Add(options.SheetName);

            // 创建表格标题
            rowNo = 1;
            columnNo = 1;
            for(int i = 0;i< dataTable.Columns.Count; i++, columnNo++)
            {
            #if DEBUG
                Console.WriteLine("{0},{1},{2}", rowNo, columnNo, dataTable.Columns[i].ColumnName);
            #endif
                sheet.Cells.Add(rowNo, columnNo, dataTable.Columns[i].ColumnName);
            }

            // 创建正文数据
            rowNo++;
            for (int i = 0; i < dataTable.Rows.Count; i++, rowNo++)
            {
                DataRow row = dataTable.Rows[i];

                columnNo = 1;
                for (int j = 0; j < columnNum; j++, columnNo++)
                {
                    string value = Convert.ToString(row[j]);
            #if DEBUG
                    Console.WriteLine("{0},{1},{2}", rowNo, columnNo, value);
            #endif
                    sheet.Cells.Add(rowNo, columnNo, value);
                }
            }

            // 保存文件
            trySaveXlsFile(xls, saveFileName);
        }