mvc Dapper_Report_Down_ExcelFile
2024-08-21 12:22:25
一、基于Aspose.Cells、Dapper导出Excel
Dapper的Query返回要不是对象的IEnumerable，要不是Dynamic的IEnumerable，都不适合不用反射就能够动态获取各个属性值得情况，而反射浪费性能是大伙儿共同认知的，Down下来Dapper源码发现返回Dynamic的IEnumerable，是返回一个Dapper内部的对象DapperRow的Dynamic的IEnumerable，而DapperRow是可以通过属性索引的，当然需要做一下小的改变：
这就好办了，如下是基于Aspose.Cells
关于Aspose.Cells可以基于excel模板来生成Excel，这点解释如下：
- &=DataSource.Field，&=[DataSource].[Field]是对DataTable或集合类型的引用，将会从当前行开始竖直向下生成多行数据。
- &=$data:是对变量或数组的引用。数组存在skip，horizontal等属性
- &=&=动态公式计算；{r}当前行，{c}当前列，{-n}，{n}当前行或列的偏移量前n或后n。
- &==是动态计算，如excel，if等语句。（if（logic_test,true_value,false_value））
public struct KeyValueDic
{
public KeyValueDic(string key,string value)
{
this.Key = key;
this.Value = value;
this.IsMutilText = false;
this.MatchText = null;
this.MatchTextDic = null;
} public KeyValueDic(string key, string value,bool isMutilText)
{
this.Key = key;
this.Value = value;
this.IsMutilText = isMutilText;
this.MatchText = null;
this.MatchTextDic = null;
} public KeyValueDic(string key, string value, string matchText)
{
Key = key;
Value = value;
IsMutilText = false;
MatchText = matchText;
MatchTextDic = null;
MatchTextDic = GetDic(matchText);
} public KeyValueDic(string key, string value, bool isMutilText, string matchText)
{
Key = key;
Value = value;
IsMutilText = isMutilText;
MatchText = matchText;
MatchTextDic = null;
MatchTextDic = GetDic(matchText);
}
public string Key { get; set; }//列名 public string Value { get; set; }//列中文名 public bool IsMutilText { get; set; }//是否是大字段 public string MatchText { get; set; }//需要匹配的文本，例sex=0；1，0;男,1;女,other;未知 public Dictionary<string, string> MatchTextDic { get; private set; } private Dictionary<string, string> GetDic(string text)
{
if (string.IsNullOrEmpty(text))
return null;
string[] _strs = text.Split(',');
if (_strs.Length == )
return null;
Dictionary<string, string> _dic = new Dictionary<string, string>();
foreach (string item in _strs)
{
string[] _strItems = item.Split(';');
if (_strItems.Length != ||string.IsNullOrEmpty(_strItems[]))
return null;
_dic.Add(_strItems[], _strItems[]);
}
return _dic;
} }
public class ExcelUtils
{
private static Style HeadStyle(Workbook workbook)//头部样式
{
//为单元格添加样式
Aspose.Cells.Style headStyle = workbook.Styles[workbook.Styles.Add()];
headStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
headStyle.Pattern = BackgroundType.Solid;
headStyle.Font.IsBold = true;
headStyle.ForegroundColor = System.Drawing.Color.Blue;
headStyle.Font.Color = System.Drawing.Color.White;
headStyle.Font.IsSubscript = true;
headStyle.Font.Size = ;
headStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
return headStyle;
} private static Style BodyStyle(Workbook workbook)//文本样式
{
Aspose.Cells.Style bodyStyle = workbook.Styles[workbook.Styles.Add()];
bodyStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
bodyStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
bodyStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
bodyStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
bodyStyle.IsTextWrapped = true;
return bodyStyle;
} public static Stream SaveToStream(IEnumerable<DapperRow> list, List<KeyValueDic> listColumns, string sheetName)
{
if (list == null || list.Count() == || listColumns == null || listColumns.Count == || listColumns.Count > list.ElementAt().Count())
throw new Exception("Excel数据源不匹配导致无法保存！"); Workbook workbook = new Workbook();
Worksheet sheet = (Worksheet)workbook.Worksheets[];
sheet.Name = sheetName; for (int i = ; i < listColumns.Count; i++)
{
sheet.Cells[, i].PutValue(listColumns[i].Value);
if (listColumns[i].IsMutilText)
sheet.Cells.SetColumnWidth(i, );
}
sheet.Cells.CreateRange(, , , listColumns.Count).SetStyle(HeadStyle(workbook));
int row = ;
foreach (var item in list)
{
row++;
for (int i = ; i < listColumns.Count; i++)
{
object val = item[listColumns[i].Key];
if (val == null)
continue;
if (val.GetType() == typeof(DateTime))//时间格式
{
DateTime dtime = (DateTime)val;
if (dtime.Hour == || dtime.Minute == || dtime.Second == )
sheet.Cells[row, i].PutValue(dtime.ToString("yyyy-MM-dd"));
else
sheet.Cells[row, i].PutValue(dtime.ToString("yyyy-MM-dd HH:mm:ss"));
}
else if (listColumns[i].MatchText != null && listColumns[i].MatchTextDic != null)//是否有需要匹配的文本
{
string _val = val.ToString();
if (listColumns[i].MatchTextDic.ContainsKey(_val))
sheet.Cells[row, i].PutValue(listColumns[i].MatchTextDic[_val]);
else
sheet.Cells[row, i].PutValue(listColumns[i].MatchTextDic["other"]);
}
else
sheet.Cells[row, i].PutValue(val.ToString());
}
}
sheet.Cells.CreateRange(, , row, listColumns.Count).SetStyle(BodyStyle(workbook));
for (int i = ; i < listColumns.Count; i++)
{
if (!listColumns[i].IsMutilText)
sheet.AutoFitColumn(i);
}
sheet.AutoFitRows();
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Excel97To2003);
ms.Position = ;
return ms;
} }
logic:
public static class Utils
{
private static log4net.ILog log = log4net.LogManager.GetLogger("FMDS"); /// <summary>
/// 将指定的字符串信息写入日志。
/// </summary>
/// <param name="message">需要写入日志的字符串信息。</param>
public static void Log(string message)
{
log.Info(message);
}
/// <summary>
/// 将指定的异常实例详细信息写入日志
/// </summary>
public static void Log(Exception ex)
{
log.Error("Exception:", ex);
}
/// <summary>
/// 将指定的异常实例详细信息写入日志。
/// </summary>
public static void Log(object message, Exception e)
{
log.Error(message, e);
} public static string TrimOrNull(this string str)
{
return str == null ? null : str.Trim();
} }
public class DbConnection
{
public static SqlConnection OpenConnection()
{
if (string.IsNullOrEmpty(ConfigUtils.ConStr))
{
Utils.Log("数据库连接失败：没有读取到FMDS链接字符串。");
return null;
}
SqlConnection connection = new SqlConnection(ConfigUtils.ConStr);
try
{
connection.Open();
return connection;
}
catch (Exception ex)
{
Utils.Log("数据库FMDS连接失败：" + ex.Message);
return null;
}
}
}
public Stream Export()
{ string sql = @""; var param = new DynamicParameters();
param.Add("mydate", DateTime.Today, System.Data.DbType.DateTime); using (SqlConnection conn = DbConnection.OpenFMJConnection())
{
if (conn == null)
return null;
var list=conn.Query<SqlMapper.DapperRow>(sql, param);
List<KeyValueDic> listKeyValue = new List<KeyValueDic>()
{
new KeyValueDic("Column1","列1"),
new KeyValueDic("Column2","列2","0;男,1;女,other;未知"),
new KeyValueDic("Column3","列3",true)
};
System.IO.Stream stream = ExcelUtils.SaveToStream(list, listKeyValue, "excelSheetName");
return stream;
}
}
Action中使用： public FileResult Export()
{
Stream stream = logic.Export();
return File(stream, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + "—excelName.xls");
}//"application/ms-excel",
View中使用： window.location.href = "@Url.Action("Export")";
二、基于System.Web.UI.WebControls.DataGrid、Ado.net导出Excel public void ExportExcel(DataTable dt, string filename)
{
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
//使用System.Web.UI.WebControls的DataGrid导出excel
DataGrid excel = new DataGrid();
System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();//标题行样式
headerStyle.BackColor = System.Drawing.Color.LightGray;
headerStyle.Font.Bold = true;
headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
excel.HeaderStyle.MergeWith(headerStyle);
System.Web.UI.WebControls.TableItemStyle alternatingStyle = new TableItemStyle();//隔行样式
alternatingStyle.BackColor = System.Drawing.Color.LightGray;
excel.AlternatingItemStyle.MergeWith(alternatingStyle);
System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();//单元格样式
itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
excel.ItemStyle.MergeWith(itemStyle); excel.GridLines = GridLines.Both;
excel.HeaderStyle.Font.Bold = true;
excel.DataSource = dt.DefaultView;//输出DataTable的内容
excel.DataBind();
excel.RenderControl(htmlWriter);
MemoryStream stream = new MemoryStream();
System.IO.StreamWriter sw = new StreamWriter(stream, System.Text.Encoding.UTF8);
sw.Write(stringWriter.ToString());
sw.Flush();
DownResult(stream, filename);
} public ActionResult DownResult(Stream stream,string fileName)
{
stream.Position = ;
const int ChunkSize = ;
byte[] buffer = new byte[ChunkSize];
long dataLengthToRead = stream.Length;
Response.Clear();
Response.Charset = "UTF-8";
Response.ContentType = "application/octet-stream";
Response.AppendHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.AppendHeader("Content-Length", dataLengthToRead.ToString());
Response.Filter.Close();
while (dataLengthToRead > && Response.IsClientConnected)
{
int lengthRead = stream.Read(buffer, , ChunkSize);
Response.OutputStream.Write(buffer, , lengthRead);
Response.Flush();
dataLengthToRead = dataLengthToRead - lengthRead;
}
stream.Close();
stream.Dispose();
Response.Flush();
/*****
这里可以做一些自己的处理，比如把谁下载的文件信息存到库里，但是这个时候，就不能用Response.Close();了，否则报下载失败，网络错误。这里可以使用如下(需放在自己的处理程序之后)：
参考：https://blogs.msdn.microsoft.com/aspnetue/2010/05/25/response-end-response-close-and-how-customer-feedback-helps-us-improve-msdn-documentation/
http://stackoverflow.com/questions/5533068/httpcontext-throws-httpexception
http://stackoverflow.com/questions/1087777/is-response-end-considered-harmful
if (Response.IsClientConnected)
{
Response.SuppressContent = true;//Prevents any other content from being sent to the browser
HttpContext.ApplicationInstance.CompleteRequest();//Directs the thread to finish, bypassing additional processing
}
GC.Collect();
*****/
Response.Close();//不要直接用Response.End();
return new EmptyResult();
}
附：以上代码偶尔仍回抛如下异常“System.Web.HttpException (0x80070057): 远程主机关闭了连接。错误代码是 0x80070057。”
突然在异常错误日志中看到这个错误，虽然在测试中发现不影响流的传输，但是不代表没错误，解决方法如下：
Response.Flush(); Response.End();//结束文件下载，但是程序在运行时会报出“正在中止线程”的错误，可以确定的是引起错误的代码就是Response.End()，所以需要删除此方法。 在IIS .5下这样做问题就解决了，不过在IIS 7.0 下又会报出另外一个错误“与远程主机通信时发生错误。错误代码是 0x800704CD。” 在网上翻查了一翻资料后，终于找到了解决方法： 就是将Response.End();换成Response.Close(); 总结一下，帮助对Response.Close的解释是关闭到客户端的连接。对Response.End的解释是停止该页的执行，并引发Application_EndRequest。也就说用Response.End程序就直接停止后面的工作了转而触发Application_EndRequest，那么当程序在后面还有代码需要运行时，程序就会抛出ThreadAbortException的异常。还有需要了解的就是end方法在不抛出异常时还会调用flush的方法。接着来，close方法是关闭了连接，也就说程序顺利执行完了所有代码后关闭了连接。对于只运行flush后报出的“与远程主机通信时发生错误”，我的理解是当执行了flush后在底层马上开始向客户端发送数据，但是flush貌似只能指示 程序开始连接发送，却没有停止关闭的标识，导致程序报出异常。当然这是在IIS .0下会出错（可能在IIS 7.0 开始在调用flush后需要显示关闭socket连接）。所以以后再在用完flush后最好加上close。就像数据库连接一样，用完就关闭连接。
如下是Response.End()的source code：
public void End()
{
if (this._context.IsInCancellablePeriod)
{
InternalSecurityPermissions.ControlThread.Assert();
Thread.CurrentThread.Abort(new HttpApplication.CancelModuleException(false));
}
else if (!this._flushing)
{
this.Flush();
this._ended = true;
if (this._context.ApplicationInstance != null)
{
this._context.ApplicationInstance.CompleteRequest();
}
}
}
也就是说使用Response.End()是包含了Response.Flush()的功能的。
附：Response.End()同样是报（偶尔情况下）：
at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect)
at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush()
at System.Web.HttpResponse.Flush(Boolean finalFlush)
at System.Web.HttpResponse.Flush()
修改为如下（待测试吧）：
//Response.End();
HttpContext.ApplicationInstance.CompleteRequest();
最新文章
- [BZOJ3754]Tree之最小方差树
- .net开发笔记（十六） 对前部分文章的一些补充和总结
- 无法启动此程序,因为计算机中丢失AdbWinApi.dll。尝试重新安装该程序以解决此问题
- javascript 之注意url特殊字符限制
- C按格式输出数字
- 【C/C++】BinarySearch
- HDU1272 小希的迷宫 并查集
- 一起学JUCE之HashMap
- HDU1159-Common Subsequence-LCS
- Y1吐槽002 情绪
- ip锁死怎么设置ip地址
- 线程（Thread、ThreadPool）
- 老牌开源Office操作组件NPOI现已支持.NET Core
- Educational Codeforces Round 52 (Rated for Div. 2) -C
- sql server 按年月日分组
- JSP标准标签库：JSTL
- TeamViewer一直显示“未就绪，请检查您的连接”什么原因？
- MySQL 插件CONNECTION_CONTROL和CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
- jQuery Easy UI Tooptip(提示框)组件
- u3d DontDestroyOnLoad多场景问题