コード例 #1
0
        public MemoryStream Export(DataTable data, string title = "No title", string subtitle = "No subtitle")
        {
            var converter = new ExcelConverter();
            var worksheet = converter.CreateSheet();

            var columns = new List <DataColumn>();

            for (var i = 0; i < data.Columns.Count; i++)
            {
                if (data.Columns[i].ExtendedProperties.ContainsKey("ExcelIgnore"))
                {
                    continue;
                }

                columns.Add(data.Columns[i]);
            }

            var ttrow = worksheet.CreateRow(0);

            ttrow.HeightInPoints = 30;
            for (var i = 0; i < columns.Count; i++)
            {
                var ttcell = ttrow.CreateCell(i);

                ttcell.SetCellValue(new XSSFRichTextString(i == 0 ? title : ""));
                ttcell.CellStyle = converter.Title;
            }
            worksheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Count - 1));

            var strow = worksheet.CreateRow(1);

            strow.HeightInPoints = 20;
            for (var i = 0; i < columns.Count; i++)
            {
                var stcell = strow.CreateCell(i);

                stcell.SetCellValue(new XSSFRichTextString(i == 0 ? subtitle : ""));
                stcell.CellStyle = converter.SubTitle;
            }
            worksheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, columns.Count - 1));

            var ctrow = worksheet.CreateRow(2);

            ctrow.HeightInPoints = 20;
            for (var i = 0; i < columns.Count; i++)
            {
                var cell       = ctrow.CreateCell(i);
                var properties = columns[i].ExtendedProperties;
                cell.SetCellValue(properties.ContainsKey("ExcelDisplayName") ? properties["ExcelDisplayName"].ToString() : columns[i].ColumnName);
                cell.CellStyle = converter.CellTitle;

                //set cell column width
                worksheet.SetColumnWidth(i, 25 * 150);
            }

            for (int k = 0; k < data.Rows.Count; k++)
            {
                var row = (XSSFRow)worksheet.CreateRow(3 + k);
                row.HeightInPoints = 18;

                for (int g = 0; g < columns.Count; g++)
                {
                    var cell   = row.CreateCell(g);
                    var column = columns[g];
                    var type   = column.DataType;
                    var value  = data.Rows[k][column.ColumnName];

                    cell.CellStyle = converter.GetCellStyle(Color.Empty, false);
                    if (value == null)
                    {
                        continue;
                    }

                    if (type == typeof(Int16) ||
                        type == typeof(Int32) ||
                        type == typeof(Int64) ||
                        type == typeof(Single) ||
                        type == typeof(Double))
                    {
                        cell.SetCellValue(Convert.ToDouble(value));
                    }
                    else if (type == typeof(Boolean))
                    {
                        cell.SetCellValue((Boolean)value);
                    }
                    else if (type == typeof(DateTime))
                    {
                        cell.SetCellValue((DateTime)value);
                    }
                    else
                    {
                        cell.SetCellValue(new XSSFRichTextString(value.ToString()));
                    }
                }
            }

            var ms = new MemoryStream();

            converter.Workbook.Write(ms);
            return(ms);
        }
コード例 #2
0
        public MemoryStream Export <T>(List <T> data, string title = "No title", string subtitle = "No subtitle")
        {
            var converter = new ExcelConverter();
            var worksheet = converter.CreateSheet();

            PropertyInfo backgroundAttribute = null;
            var          colorAttributes     = new List <string>();
            var          boolAttributes      = new List <Kv <PropertyInfo, ExcelBooleanNameAttribute> >();
            var          dataAttributes      = new List <Kv <PropertyInfo, string> >();

            var props = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);

            foreach (var prop in props)
            {
                if (prop.IsDefined(typeof(ExcelIgnoreAttribute), true))
                {
                    continue;
                }

                if (prop.IsDefined(typeof(ExcelBackgroundAttribute), true))
                {
                    if (prop.PropertyType == typeof(Color))
                    {
                        backgroundAttribute = prop;
                    }

                    continue;
                }

                if (prop.IsDefined(typeof(ExcelColorAttribute), true))
                {
                    colorAttributes.Add(prop.Name);
                }

                if (prop.IsDefined(typeof(ExcelBooleanNameAttribute), true))
                {
                    if (prop.PropertyType == typeof(Boolean))
                    {
                        var boolean = (ExcelBooleanNameAttribute)prop.GetCustomAttributes(typeof(ExcelBooleanNameAttribute), true)[0];
                        boolAttributes.Add(new Kv <PropertyInfo, ExcelBooleanNameAttribute> {
                            Key = prop, Value = boolean
                        });
                    }
                }

                if (prop.IsDefined(typeof(ExcelDisplayNameAttribute), true))
                {
                    var display = (ExcelDisplayNameAttribute)prop.GetCustomAttributes(typeof(ExcelDisplayNameAttribute), true)[0];
                    dataAttributes.Add(new Kv <PropertyInfo, string>(prop, display.DisplayName));
                }
                else
                {
                    dataAttributes.Add(new Kv <PropertyInfo, string>(prop, prop.Name));
                }
            }

            var ttrow = worksheet.CreateRow(0);

            ttrow.HeightInPoints = 30;
            for (var i = 0; i < dataAttributes.Count; i++)
            {
                var ttcell = ttrow.CreateCell(i);

                ttcell.SetCellValue(new XSSFRichTextString(i == 0 ? title : ""));
                ttcell.CellStyle = converter.Title;
            }
            worksheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dataAttributes.Count - 1));

            var strow = worksheet.CreateRow(1);

            strow.HeightInPoints = 20;
            for (var i = 0; i < dataAttributes.Count; i++)
            {
                var stcell = strow.CreateCell(i);

                stcell.SetCellValue(new XSSFRichTextString(i == 0 ? subtitle : ""));
                stcell.CellStyle = converter.SubTitle;
            }
            worksheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dataAttributes.Count - 1));

            var ctrow = worksheet.CreateRow(2);

            ctrow.HeightInPoints = 20;
            for (var i = 0; i < dataAttributes.Count; i++)
            {
                var cell = ctrow.CreateCell(i);
                cell.SetCellValue(dataAttributes[i].Value);
                cell.CellStyle = converter.CellTitle;

                //set cell column width
                worksheet.SetColumnWidth(i, 25 * 150);
            }

            for (int k = 0; k < data.Count; k++)
            {
                var row = (XSSFRow)worksheet.CreateRow(3 + k);
                row.HeightInPoints = 18;

                var background = Color.Empty;
                if (backgroundAttribute != null)
                {
                    background = (Color)backgroundAttribute.GetValue(data[k]);
                }

                for (int g = 0; g < dataAttributes.Count; g++)
                {
                    var cell  = row.CreateCell(g);
                    var prop  = dataAttributes[g].Key;
                    var draw  = colorAttributes.Contains(prop.Name);
                    var name  = prop.Name;
                    var type  = prop.PropertyType;
                    var value = prop.GetValue(data[k]);

                    if (value == null)
                    {
                        cell.CellStyle = converter.GetCellStyle(background, draw);
                        continue;
                    }

                    if (type == typeof(Int16) ||
                        type == typeof(Int32) ||
                        type == typeof(Int64) ||
                        type == typeof(Single) ||
                        type == typeof(Double))
                    {
                        cell.CellStyle = converter.GetCellStyle(background, draw);
                        cell.SetCellValue(Convert.ToDouble(value));
                    }
                    else if (type == typeof(Boolean))
                    {
                        cell.CellStyle = converter.GetCellStyle(background, draw);

                        var boolAttribute = boolAttributes.Find(b => b.Key.Equals(prop));
                        if (boolAttribute != null)
                        {
                            cell.SetCellValue(new XSSFRichTextString((Boolean)value ? boolAttribute.Value.True : boolAttribute.Value.False));
                        }
                        else
                        {
                            cell.SetCellValue((Boolean)value);
                        }
                    }
                    else if (type == typeof(DateTime))
                    {
                        cell.CellStyle = converter.GetDateCellStyle(background, draw);
                        cell.SetCellValue((DateTime)value);
                    }
                    else
                    {
                        cell.CellStyle = converter.GetCellStyle(background, draw);
                        cell.SetCellValue(new XSSFRichTextString(value.ToString()));
                    }
                }
            }

            var ms = new MemoryStream();

            converter.Workbook.Write(ms);
            return(ms);
        }