导出Excel代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】导出Excel
作者:Dezai.CN / 发布于2013/6/13/ 552

	/// <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.DataTable dtTemp,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;

	 int dcell = 1;
	 int rowindex = 0; int colindex = 0;

	 int rowcount = dtTemp.Rows.Count;
	 int colcount = dtTemp.Columns.Count;
	 try
	 {
	 //初始化 Application 对象 excelApp
	 excelApp = new Application();
	 //在工作薄的第一个工作表上创建任务列表
	 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
	 ws = (Worksheet)workBook.Worksheets[1];

	 // 命名工作表的名称为
	 ws.Name = !string.IsNullOrEmpty(sName) ? sName : "Sheet1" ;
	 //创建缓存
	 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.xlCenter; //全局左对齐
	 excelApp.Cells.EntireColumn.AutoFit();
	 range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount + 1, colcount]);
	 range.Borders.LineStyle = 1;
	 //range.Font.Bold = true; //标题粗体
	 //显示 Excel
	 //excelApp.Visible = true;
	 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;
	 }
	 }

	
	 /// <summary>
	 /// 导出Excel
	 /// </summary>
	 /// <param name="dtTemp"></param>
	 public static void ExportCMB(System.Data.DataTable dtTemp, string ServerPath, string TextName)
	 {
	 // 定义要使用的Excel 组件接口
	 // 定义Application 对象,此对象表示整个Excel 程序
	 Application excelApp = null;
	 // 定义Workbook对象,此对象代表工作薄
	 Workbook workBook;
	 // 定义Worksheet 对象,此对象表示Execel 中的一张工作表
	 Worksheet ws = null;
	 //定义Range对象,此对象代表单元格区域
	 Range range;

	 int dcell = 1;
	 int colindex = 0;

	 int rowcount = dtTemp.Rows.Count;
	 int colcount = 14;
	 try
	 {
	 //初始化 Application 对象 excelApp
	 excelApp = new Application();
	 //在工作薄的第一个工作表上创建任务列表
	 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
	 ws = (Worksheet)workBook.Worksheets[1];

	 // 命名工作表的名称为
	 ws.Name = "Sheet1";
	 //创建缓存
	 Object[,] objdata = new object[rowcount + 3, colcount];
	 objdata[0, 0] = "代发工资表";
	 objdata[1, 0] = "TF:QY1CWS1";
	 //创建标题
	 foreach (System.Data.DataColumn dc in dtTemp.Columns)
	 {
	 objdata[2, colindex++] = dc.ColumnName;
	 }
	 //获取数据
	 for (int i = 0; i < rowcount; i++)
	 {
	 dcell = 0;

	 for (int j = 0; j < colcount; j++)
	 {
	 objdata[i + 3, dcell++] = dtTemp.Rows[i][dtTemp.Columns[j].ColumnName].ToString();
	 }
	 }
	 //写入Excel
	 range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount + 3, colcount]);
	 ws.get_Range(excelApp.Cells[4, 12], excelApp.Cells[rowcount + 3, 12]).NumberFormatLocal = "@";
	 //range.NumberFormatLocal = "@";
	 range.Value2 = objdata;
	 range.Font.Size = 10;
	 System.Windows.Forms.Application.DoEvents();
	 //设置格式
	 excelApp.Cells.HorizontalAlignment = Constants.xlLeft; //全局左对齐
	 excelApp.Cells.EntireColumn.AutoFit();
	 range = ws.get_Range(excelApp.Cells[3, 1], excelApp.Cells[rowcount + 3, colcount]);
	 //range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
	 range.Borders.LineStyle = 1;
	 //显示 Excel
	 //excelApp.Visible = true;
	 workBook.SaveCopyAs(ServerPath.Replace("//", "\\") + TextName + ".xls");
	 workBook.Close(false, null, null);
	 excelApp.Quit();
	 ws = null;

	 }
	 catch (Exception ex)
	 {
	 WriteLog.SetErrorMsg("ExportICBC", "", ex.Message); //处理错误
	 excelApp.Quit();
	 throw ex;
	 }
	 }
	 }
试试其它关键字
　导出Excel　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)