/// <summary>
/// 导出表格的所有数据到Excel,DataTable名称对应sheet的名称
/// </summary>
/// <param name="dt">DataTable对象，DataTable名称对应sheet的名称，如果名称为空则sheet名称为Sheet1</param>
/// <param name="path">Excel保存目录</param>
/// <param name="fileName">Excel文件名，包含扩展名</param>
public static void DataTableToExcel(this DataTable dt, string path, string fileName)
{
AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
doc.FileName = fileName;
string sheetName = dt.TableName;
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "Sheet1";
}
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(sheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
for (int i = 0; i < dt.Columns.Count; i++)
{
AppLibrary.WriteExcel.XF xf = doc.NewXF();
xf.Font.FontName = "宋体";
xf.Font.Bold = true;
xf.Font.Height = (ushort)(220);
xf.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
xf.VerticalAlignment = AppLibrary.WriteExcel.VerticalAlignments.Centered;
cells.Add(1, i + 1, dt.Columns[i].ColumnName, xf);
for (int j = 0; j < dt.Rows.Count; j++)
{
if (Convert.IsDBNull(dt.Rows[j][i]))
{
continue;
}
cells.Add(j + 2, i + 1, dt.Rows[j][i]);
}
}
doc.Save(path, true);
}
protected void Unnamed2_Click(object sender, EventArgs e)
{
List <ModelStu> list = StuDaTa.GetData();//集合数据
string SheetName = "请假学生表";
string[] arr = "编号,年龄,姓名,PC".Split(',');
string DocName = SheetName + DateTime.Now.ToString("yyyyMMdd_HHmmss");
#region XlsDocument对象
var doc = new AppLibrary.WriteExcel.XlsDocument();
#region 解决文件名火狐导出乱码问题
doc.FileName = Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox", StringComparison.Ordinal) != -1 ? string.Format("\"{0}\"", DocName) : System.Web.HttpUtility.UrlPathEncode(DocName);
#endregion
#endregion
var g1 = list.GroupBy(s => s.age);
foreach (var item1 in g1)
{
SheetName = "KEY值：" + item1.Key;
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
cells.Add(1, 1, SheetName).HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
sheet.Cells.Merge(1, 1, 1, arr.Length);
for (int m = 0; m < arr.Length; m++)
{
cells.Add(2, m + 1, arr[m].ToString());
}
List <ModelStu> list2 = new List <ModelStu>();
foreach (var itemtmp in item1)
{
list2.Add(itemtmp);
} //新集合
var g2 = list2.GroupBy(s => s.name);
var rowIndex = 1; //applibrary的row和col索引从1开始
foreach (var item2 in g2)
{
List <ModelStu> list3 = new List <ModelStu>();
foreach (var itemtmp in item2)
{
list3.Add(itemtmp);
} //新集合
cells.Add(rowIndex++, 1, item2.Key).HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
foreach (var item3 in list3)
{
var colIndex = 1;
cells.Add(rowIndex, colIndex++, item3.id);
cells.Add(rowIndex, colIndex++, item3.age);
cells.Add(rowIndex, colIndex++, item3.name);
cells.Add(rowIndex, colIndex++, item3.pc);
rowIndex++;
}
sheet.Cells.Merge(rowIndex, rowIndex, 1, arr.Length);
}
}
doc.Send();
Response.Flush();
Response.End();
//return File(DocName, "application/ms-excel");
}
/// <summary>
/// 将DataSet写入到Excel文件中
/// </summary>
/// <param name="table"></param>
/// <param name="fname"></param>
/// <returns></returns>
public static void ExportDataSetToExcel(DataSet ds, string fname)
{
string[,] head1 = new string[4, ds.Tables[0].Columns.Count - 1];
//string path = "D:\\";
// path = System.Configuration.ConfigurationManager.AppSettings["Email"].ToString();
AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
string Time = DateTime.Now.ToString("yyyy-MM-dd");
//doc.FileName = fname+ ".xls";
doc.FileName = HttpUtility.UrlEncode(fname, System.Text.Encoding.UTF8);
string SheetName = string.Empty;
//计算当前多少个SHEET
for (int i = 0; i < ds.Tables.Count; i++)
{
SheetName = ds.Tables[i].TableName;
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
AppLibrary.WriteExcel.XF center = doc.NewXF(); //添加样式的
center.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
center.Font.FontName = "宋体"; //字体
center.TextDirection = AppLibrary.WriteExcel.TextDirections.LeftToRight; //文本位置
center.Font.Bold = true; //加粗
AppLibrary.WriteExcel.ColumnInfo colInfo = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo.ColumnIndexStart = 0; //开始列
colInfo.ColumnIndexEnd = 20; //结束列
colInfo.Width = 5000; //列宽
colInfo.Collapsed = true;
// colInfo.Width = 150;
sheet.AddColumnInfo(colInfo);
//边框线的样式
center.DiagonalLineStyle = new AppLibrary.WriteExcel.LineStyle();
center.BottomLineStyle = 1;
center.LeftLineStyle = 1;
center.TopLineStyle = 1;
center.RightLineStyle = 1;
center.RightLineColor = AppLibrary.WriteExcel.Colors.Grey;
center.LeftLineColor = AppLibrary.WriteExcel.Colors.Grey;
center.TopLineColor = AppLibrary.WriteExcel.Colors.Grey;
center.BottomLineColor = AppLibrary.WriteExcel.Colors.Grey;
sheet.AddColumnInfo(colInfo);
AppLibrary.WriteExcel.XF Head = doc.NewXF(); //添加样式的
Head.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
Head.Font.FontName = "宋体"; //字体
Head.PatternColor = AppLibrary.WriteExcel.Colors.Default16;
//Head.TextDirection = AppLibrary.WriteExcel.TextDirections.Default;//文本位置
Head.Font.Bold = true;//加粗
Head.Pattern = 1;
Head.Font.Height = 230;
//边框线的样式
Head.DiagonalLineStyle = new AppLibrary.WriteExcel.LineStyle();
Head.BottomLineStyle = 1;
Head.LeftLineStyle = 1;
Head.TopLineStyle = 1;
Head.RightLineStyle = 1;
Head.RightLineColor = AppLibrary.WriteExcel.Colors.Grey;
Head.LeftLineColor = AppLibrary.WriteExcel.Colors.Grey;
Head.TopLineColor = AppLibrary.WriteExcel.Colors.Grey;
Head.BottomLineColor = AppLibrary.WriteExcel.Colors.Grey;
AppLibrary.WriteExcel.XF XFstyle = doc.NewXF();//添加样式的
//XFstyle.TextDirection = AppLibrary.WriteExcel.TextDirections.Left;//文本位置
XFstyle.Font.Bold = false;//加粗
//边框线的样式
XFstyle.DiagonalLineStyle = new AppLibrary.WriteExcel.LineStyle();
for (int cols = 0; cols < ds.Tables[i].Columns.Count; cols++)
{
cells.Add(1, cols + 1, ds.Tables[i].Columns[cols].ToString(), center);
}
//第一行表头
//第一行显示表的列名
//标题占四行
for (int rows = 0; rows < ds.Tables[i].Rows.Count; rows++)
{
for (int cols = 0; cols < ds.Tables[i].Columns.Count; cols++)//因为表一有一列城市是不需要的 所以采用这种写法
{
cells.Add(rows + 2, cols + 1, ds.Tables[i].Rows[rows][cols].ToString());
}
}
}
GC.Collect();
doc.Send();
}
protected void Page_Load(object sender, EventArgs e)
{
list = new DapperHelper().Instance().Query <PVModel>(" select * from PV order by createtime desc ").ToList();
string action = System.Web.HttpContext.Current.Request["action"];
if (!string.IsNullOrEmpty(action) && action == "submit")
{
var mobile = System.Web.HttpContext.Current.Request["mobile"];
var starttime = System.Web.HttpContext.Current.Request["start"];
var endtime = System.Web.HttpContext.Current.Request["end"];
var where = " where 1 = 1 ";
if (!string.IsNullOrEmpty(mobile))
{
where = where + $" and mobile like '%{mobile}%' ";
}
if (!string.IsNullOrEmpty(starttime))
{
where = where + $" and createtime >= '{starttime.Replace("T"," ")}' ";
}
if (!string.IsNullOrEmpty(endtime))
{
where = where + $" and createtime >= '{endtime.Replace("T", " ")}' ";
}
var items = new DapperHelper().Instance().Query <PVModel>($" select * from PV {where} order by createtime desc ").ToList();
items.ForEach(x => { x.ShowTime = x.CreateTime.ToString("yyyy/MM/dd HH:mm"); });
Response.Write(JsonConvert.SerializeObject(items));
Response.End();
}
if (!string.IsNullOrEmpty(action) && action == "import")
{
var mobile = System.Web.HttpContext.Current.Request["mobile"];
var starttime = System.Web.HttpContext.Current.Request["start"];
var endtime = System.Web.HttpContext.Current.Request["end"];
var where = " where 1 = 1 ";
if (!string.IsNullOrEmpty(mobile))
{
where = where + $" and mobile like '%{mobile}%' ";
}
if (!string.IsNullOrEmpty(starttime))
{
where = where + $" and createtime >= '{starttime.Replace("T", " ")}' ";
}
if (!string.IsNullOrEmpty(endtime))
{
where = where + $" and createtime >= '{endtime.Replace("T", " ")}' ";
}
var items = new DapperHelper().Instance().Query <PVModel>($" select * from PV {where} order by createtime desc ").ToList();
items.ForEach(x => { x.ShowTime = x.CreateTime.ToString("yyyy/MM/dd HH:mm"); });
AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
doc.FileName = "PV.xlxs";
string SheetName = "PV";
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
//第一行表头
cells.Add(1, 1, "手机号");
cells.Add(1, 2, "渠道");
cells.Add(1, 3, "时间");
int f = 1;
for (int m = 0; m < items.Count(); m++)
{
f++;
cells.Add(f, 1, items[m].Mobile);
cells.Add(f, 2, items[m].Code);
cells.Add(f, 3, items[m].ShowTime);
}
doc.Send();
}
}
