Excel导出

static void BuildStringOfRow(StringBuilder strBuilder, List<string> lstFields, string strFormat)
        {
            switch (strFormat)
            {
                case "XLS":
                    strBuilder.AppendLine("<Row>");
                    strBuilder.AppendLine(String.Join("\r\n", lstFields.ToArray()));
                    strBuilder.AppendLine("</Row>");
                    break;
                case "CSV":
                    strBuilder.AppendLine(String.Join(",", lstFields.ToArray()));
                    break;
            }
        }

        static void FormatCellWidth(StringBuilder strBuilder, List<double> colsWidth, string strFormat)
        {
            var index = 1;
            colsWidth.ForEach(p =>
            {
                switch (strFormat)
                {
                    case "XLS":
                        strBuilder.AppendLine(string.Format(@"<Column ss:Index='{0}' ss:AutoFitWidth='0' ss:Width='{1}'/>", index, colsWidth[index - 1]));
                        break;
                }
                index++;
            });
        }

        static string FormatField(string data, HorizontalAlignment alignment)
        {
            return FormatField(data, "XLS", false, alignment);
        }

        static string FormatField(string data, bool isHead)
        {
            return FormatField(data, "XLS", isHead, HorizontalAlignment.Left);
        }

        static string FormatField(string data, string format, bool isHead, HorizontalAlignment alignment)
        {
            //替换< , >否则，导出的Excel打不开
            data = data.Replace("<", "	<").Replace(">", ">");
            switch (format)
            {
                case "XLS":
                    if (isHead)
                    {
                        return String.Format("<Cell><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data);
                        //return String.Format("<Cell ss:StyleID=\"HeaderStyle\"><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data);

                    }
                    else
                    {
                        return String.Format("<Cell><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data);
                        //return String.Format("<Cell ss:StyleID=\"" + GetAlignmentType(alignment) + "\"><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data);
                    }
                case "CSV":
                    return String.Format("\"{0}\"",
                      data.Replace("\"", "\"\"\"").Replace("\n",
                      "").Replace("\r", ""));
            }
            return data;
        }

        static string FormatMergeField(string data, string format, HorizontalAlignment alignment, int mergeDownCount)
        {
            //替换< , >否则，导出的Excel打不开
            data = data.Replace("<", "	<").Replace(">", ">");
            return String.Format("<Cell ss:MergeDown=\"{1}\" ><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data, mergeDownCount);
            //return String.Format("<Cell ss:MergeDown=\"{1}\" ss:StyleID=\"" + GetAlignmentType(alignment) + "\"><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data, mergeDownCount);

        }

        static string FormatFollowMergeField(string data, string format, HorizontalAlignment alignment, int mergeDownIndex)
        {
            //替换< , >否则，导出的Excel打不开
            data = data.Replace("<", "	<").Replace(">", ">");
            return String.Format("<Cell ss:Index=\"{1}\" ><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data, mergeDownIndex);
            //return String.Format("<Cell ss:Index=\"{1}\" ss:StyleID=\"" + GetAlignmentType(alignment) + "\"><Data ss:Type=\"String" + "\">{0}</Data></Cell>", data, mergeDownIndex);

        }

        static string GetAlignmentType(HorizontalAlignment alignment)
        {
            switch (alignment)
            {
                case HorizontalAlignment.Left:
                    return "CellLeftStyle";
                case HorizontalAlignment.Center:
                    return "CellCenterStyle";
                case HorizontalAlignment.Right:
                    return "CellRightStyle";
                default:
                    return "CellLeftStyle";
            }
        }

        public static Byte[] Export<T>(List<ColumnConfig> columnsConfigs, List<T> data, string mergeByName)
        {
            //record each row data.
            List<string> lstFields = new List<string>();
            //temporarily record each column width.
            List<double> colsWidth = new List<double>();
            //Record final excel xml.
            StringBuilder strBuilder = new StringBuilder();
            AppendPrefixXml(strBuilder);
            var type = typeof(T);

            //build excel header
            Dictionary<ColumnConfig, PropertyInfo> properties = new Dictionary<ColumnConfig, PropertyInfo>();
            foreach (ColumnConfig columnConfig in columnsConfigs)
            {
                lstFields.Add(FormatField(columnConfig.Name, true));
                colsWidth.Add(columnConfig.Width);
                if (!properties.ContainsKey(columnConfig))
                {
                    var propertyName = columnConfig.PropertyName;
                    var property = type.GetProperty(propertyName);
                    properties.Add(columnConfig, property);
                }
            }
            FormatCellWidth(strBuilder, colsWidth, "XLS");
            BuildStringOfRow(strBuilder, lstFields, "XLS");


            #region Build excel body
            int mergeCount = -1;
            int mergeCountCopy = -1;
            int columnIndex = 0;
            bool isNeedMerge = false;
            var megreProperty = type.GetProperty(mergeByName);
            Dictionary<string, List<T>> groupData = new Dictionary<string, List<T>>();
            string tempSONumber = "-1";
            foreach (var item in data)
            {
                string megreValue = Convert.ToString(megreProperty.GetValue(item, null));
                if (tempSONumber != megreValue)
                {
                    tempSONumber = megreValue;
                }

                if (groupData.ContainsKey(tempSONumber))
                {
                    groupData[tempSONumber].Add(item);
                }
                else
                {
                    List<T> list = new List<T>();
                    list.Add(item);
                    groupData.Add(tempSONumber, list);
                }
            }
            foreach (var item in data)
            {
                lstFields.Clear();
                columnIndex = 0;

                if (mergeCount == -1)
                {
                    string megreValue =Convert.ToString(megreProperty.GetValue(item, null));
                    if (groupData.ContainsKey(megreValue))
                    {
                        mergeCount = groupData[megreValue].Count - 1;
                    }
                    if (mergeCount > 0)
                    {
                        isNeedMerge = true;
                        mergeCountCopy = mergeCount;
                    }
                    else
                    {
                        mergeCount = -1;
                        mergeCountCopy = mergeCount;
                        isNeedMerge = false;
                    }
                }
                foreach (KeyValuePair<ColumnConfig, PropertyInfo> keyValue in properties)
                {
                    columnIndex++;
                    var value = keyValue.Value.GetValue(item, null);
                    //drawing first merge data
                    if (isNeedMerge && mergeCountCopy == mergeCount)
                    {
                        if (keyValue.Key.IsMergeDown)
                        {
                            lstFields.Add(FormatMergeField(value == null ? string.Empty : value.ToString(), "XLS", keyValue.Key.TextAlign, mergeCount));
                        }
                        else
                        {
                            lstFields.Add(FormatField(value == null ? string.Empty : value.ToString(), keyValue.Key.TextAlign));
                        }
                    }
                    //drawing follow data
                    else if (isNeedMerge && mergeCountCopy > 0)
                    {
                        if (!keyValue.Key.IsMergeDown)
                        {
                            lstFields.Add(FormatFollowMergeField(value == null ? string.Empty : value.ToString(), "XLS", keyValue.Key.TextAlign, columnIndex));
                        }
                    }
                    //drawing normal merge data
                    else
                    {
                        lstFields.Add(FormatField(value == null ? string.Empty : value.ToString(), keyValue.Key.TextAlign));
                    }
                }
                if (mergeCount > 0)
                {
                    mergeCount--;
                }
                else
                {
                    mergeCount = -1;
                }
                BuildStringOfRow(strBuilder, lstFields, "XLS");
            }
            #endregion

            //build excel footer
            AppendPostfixXml(strBuilder);
            return System.Text.Encoding.UTF8.GetBytes(strBuilder.ToString());
        }

        static void AppendPrefixXml(StringBuilder strBuilder)
        {
            strBuilder.Append("<?xml version=\"1.0\" " +
                                 "encoding=\"utf-8\"?>");
            strBuilder.Append("<?mso-application progid" +
                         "=\"Excel.Sheet\"?>");
            strBuilder.Append("<Workbook xmlns=\"urn:" +
                         "schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
            strBuilder.Append("<DocumentProperties " +
                         "xmlns=\"urn:schemas-microsoft-com:" +
                         "office:office\">");
            strBuilder.Append("<Author>Newegg.com</Author>");
            strBuilder.Append("<Created>" +
                         DateTime.Now.ToLocalTime().ToLongDateString() +
                         "</Created>");
            strBuilder.Append("<LastSaved>" +
                         DateTime.Now.ToLocalTime().ToLongDateString() +
                         "</LastSaved>");
            strBuilder.Append("<Company>Newegg.com</Company>");
            strBuilder.Append("<Version>12.00</Version>");
            strBuilder.Append("</DocumentProperties>");
            //strBuilder.Append("<Styles>");
            //strBuilder.Append("<Style ss:ID=\"HeaderStyle\"><Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>"); strBuilder.Append("<Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#FFFFFF\"/>");
            //strBuilder.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#FFFFFF\"/>");
            //strBuilder.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#FFFFFF\"/>");
            //strBuilder.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#FFFFFF\"/>");
            //strBuilder.Append("</Borders>");
            //strBuilder.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\" ss:Color=\"#FFFFFF\" ss:Bold=\"1\"/>");
            //strBuilder.Append("<Interior ss:Color=\"#008000\" ss:Pattern=\"Solid\"/>");
            //strBuilder.Append("</Style>");
            //strBuilder.Append("<Style ss:ID=\"CellCenterStyle\">");
            //strBuilder.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
            //strBuilder.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\"/>");
            //strBuilder.Append("</Style>");

            //strBuilder.Append("<Style ss:ID=\"CellLeftStyle\">");
            //strBuilder.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
            //strBuilder.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\"/>");
            //strBuilder.Append("</Style>");

            //strBuilder.Append("<Style ss:ID=\"CellRightStyle\">");
            //strBuilder.Append("<Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
            //strBuilder.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\"/>");
            //strBuilder.Append("</Style>");
            //strBuilder.Append("</Styles>");
            strBuilder.Append("<Worksheet ss:Name=\"Export Data\" " +
               "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
            strBuilder.Append("<Table>");
        }

        static void AppendPostfixXml(StringBuilder strBuilder)
        {
            strBuilder.Append("</Table>");
            strBuilder.Append("</Worksheet>");
            strBuilder.Append("</Workbook>");
        }

        public static byte[] Zip(byte[] source, string fileName)
        {
            using (MemoryStream StreamZip = new MemoryStream())
            {
                using (ZipOutputStream zipFileStream = new ZipOutputStream(StreamZip))
                {
                    zipFileStream.IsStreamOwner = true;
                    zipFileStream.SetLevel(6);

                    ZipEntry zipEntry = new ZipEntry(fileName);
                    zipEntry.DateTime = DateTime.UtcNow;
                    zipEntry.Size = source.Length;

                    zipFileStream.PutNextEntry(zipEntry);
                    zipFileStream.Write(source, 0, source.Length);
                    zipFileStream.Finish();

                    StreamZip.Position = 0;

                    byte[] resultBytes = new byte[StreamZip.Length];
                    StreamZip.Read(resultBytes, 0, (int)StreamZip.Length);
                    StreamZip.Seek(0, SeekOrigin.Begin);
                    return resultBytes;
                }
            }
        }
    }

    public class ColumnConfig
    {
        public double Width { get; set; }

        public string Name { get; set; }

        public string PropertyName { get; set; }

        public HorizontalAlignment TextAlign { get; set; }

        public bool IsMergeDown { get; set; }
        //public string DataType {get;set;}
    }

    public enum HorizontalAlignment
    {
        Left,
        Right,
        Center
    }