/// <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();
}