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"); }
public FileResult ToExcel2(string keyword) { List <stu> awardListDorm = new List <stu> { new stu { num = 1, name = "zs", age = 18 }, new stu { num = 1, name = "zs", age = 18 }, new stu { num = 1, name = "zs", age = 18 }, new stu { num = 1, name = "zs", age = 18 } }; string SheetName = "请假学生表"; string[] arr = "学号,姓名,专业".Split(','); string ExcelName = SheetName + DateTime.Now.ToString("yyyyMMdd_HHmmss"); //XlsDocument对象 var doc = new AppLibrary.WriteExcel.XlsDocument(); #region 解决文件名火狐导出乱码问题 doc.FileName = Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox", StringComparison.Ordinal) != -1 ? string.Format("\"{0}\"", ExcelName) : System.Web.HttpUtility.UrlPathEncode(ExcelName); #endregion int rowIndex = 3; int i = 1; var sheet1 = doc.Workbook.Worksheets.Add(ExcelName); var cells = sheet1.Cells; sheet1.Cells.Merge(1, 1, 1, arr.Length + 1); cells.Add(1, 1, SheetName).HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered; for (int j = 0; j < arr.Length; j++) { cells.Add(2, j + 1, arr[j].ToString()); } foreach (var item in awardListDorm) { var colIndex = 1; cells.Add(rowIndex, colIndex, i); cells.Add(rowIndex, colIndex++, item.num); cells.Add(rowIndex, colIndex++, item.name); cells.Add(rowIndex, colIndex++, item.age); rowIndex++; i++; } doc.Send(); Response.Flush(); Response.End(); return(File("555555", "application/ms-excel")); }
public void ToExcel(string keyword) { //List<AwardListClassDTO> awardListClass = awardsBLL.GetGridJsonClass(queryDto, userInfo); string ExcelName = "6S评选-班级评选表_" + DateTime.Now.ToString("yyyyMMdd_HHmmss"); //XlsDocument对象 var doc = new AppLibrary.WriteExcel.XlsDocument(); //保存的文件名 #region 解决火狐导出乱码问题 doc.FileName = Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox", StringComparison.Ordinal) != -1 ? string.Format("\"{0}\"", ExcelName) : System.Web.HttpUtility.UrlPathEncode(ExcelName); #endregion //工作簿 //单元格 int rowIndex = 3, colIndex = 1, i = 1; var sheet1 = doc.Workbook.Worksheets.Add(ExcelName); var cells = sheet1.Cells; sheet1.Cells.Merge(1, 1, 1, 6); cells.Add(1, 1, "6S评选-班级评选表").HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered; cells.Add(2, 1, "编号"); cells.Add(2, 2, "班级名称"); cells.Add(2, 3, "系部"); cells.Add(2, 4, "年届"); cells.Add(2, 5, "6S宿舍比例"); cells.Add(2, 6, "当前名次"); //foreach (AwardListClassDTO item in awardListClass) //{ // cells.Add(rowIndex, colIndex, i); // cells.Add(rowIndex, colIndex + 1, item.CLASS); // cells.Add(rowIndex, colIndex + 2, item.DEPARTMENT); // cells.Add(rowIndex, colIndex + 3, item.YEAR); // cells.Add(rowIndex, colIndex + 4, item.RATIO); // cells.Add(rowIndex, colIndex + 5, item.RANK); // rowIndex++; // i++; //} doc.Send(); Response.Flush(); Response.End(); //return File("", "application/ms-excel"); //return null; }
/// <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(); } }