/// <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); }
/// <summary> /// 实例化合并单元格操作类 /// </summary> /// <param name="cells">作簿内的所有单元格的集合</param> /// <param name="xf">表示单元格的边框、样式、字体格式等</param> public MergeCellsOpt(AppLibrary.WriteExcel.Cells cells, AppLibrary.WriteExcel.XF xf) { this.Cells = cells; this.Xf = xf; }
/// <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(); }