62,268
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dtTemp"></param>
/// <param name="ServerPath"></param>
/// <param name="TextName"></param>
/// <param name="fc">将number格式化成string</param>
public static void ExportTasks(System.Data.DataSet ds, string ServerPath, string TextName, int fc, string [] sName)
{
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Worksheet ws = null;
//定义Range对象,此对象代表单元格区域
Range range;
try
{
excelApp = new Application();
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
for (int d = 0; d<ds.Tables.Count; d++)
{
System.Data.DataTable dtTemp = ds.Tables[d];
int dcell = 1;
int rowindex = 0; int colindex = 0;
int rowcount = dtTemp.Rows.Count;
int colcount = dtTemp.Columns.Count;
//初始化 Application 对象 excelApp
//在工作薄的第一个工作表上创建任务列表
//workBook.Worksheets.Count
ws = (Worksheet)workBook.Worksheets[d+1];
// 命名工作表的名称为
if (sName.Length >= d)
{
ws.Name = !string.IsNullOrEmpty(sName[d]) ? sName[d] : "Sheet"+(d+1).ToString();
}
else
ws.Name = "Sheet" + (d + 1).ToString();
//创建缓存
Object[,] objdata = new object[rowcount + 1, colcount];
//创建标题
foreach (System.Data.DataColumn dc in dtTemp.Columns)
{
objdata[rowindex, colindex++] = dc.ColumnName;
}
//获取数据
for (int i = 0; i < rowcount; i++)
{
dcell = 0;
for (int j = 0; j < colcount; j++)
{
objdata[i + 1, dcell++] = dtTemp.Rows[i][dtTemp.Columns[j].ColumnName].ToString();
}
}
//写入Excel
range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount + 1, colcount]);
//range.NumberFormatLocal = "@";//所有字段格式化为文本格式
ws.get_Range(excelApp.Cells[2, fc], excelApp.Cells[rowcount + 1, fc]).NumberFormatLocal = "@";
range.Value2 = objdata;
System.Windows.Forms.Application.DoEvents();
//标题粗体
range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, colcount]);
range.Font.Bold = true;
//全局左对齐
excelApp.Cells.HorizontalAlignment = Constants.xlLeft;
excelApp.Cells.EntireColumn.AutoFit();
//边线
range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount + 1, colcount]);
range.Borders.LineStyle = 1;
}
workBook.SaveCopyAs(ServerPath.Replace("//", "\\") + TextName + ".xls");
workBook.Close(false, null, null);
excelApp.Quit();
ws = null;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("ExportTasks", "", ex.Message); //处理错误
excelApp.Quit();
throw ex;
}
}
DataTable dt = new DataTable();
dt = Business.BLL.GetPatientInfo(sql_condition, "CheckRecord_Page", pageCount, resultCount, 0);//获取数据库数据
if (dt.Rows.Count == 0)
{
MessageBox.Show("表数据为空,无法导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
string serverpath = "D:\\beifen\\";//设置路径
string filename = "Patientlist" + DateTime.Now.ToShortDateString();//文件名
Business.Export.ExportExcel.ExportTasks(dt, serverpath, filename, 1, "sheet1");
MessageBox.Show("数据导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}