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 }
如果您觉得本文对你有用,不妨帮忙点个赞,或者在评论里给我一句赞美,小小成就都是今后继续为大家编写优质文章的动力!
欢迎您持续关注我的博客:)
版权所有,欢迎保留原文链接进行转载:)