62,050
社区成员
发帖
与我相关
我的任务
分享
public static string Export_Excel1(System.Data.DataTable source, string savePath, string fileName, string title)
{
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
if (myExcel == null)
{
return "无法打开Excel,请检查Excel是否可用或者是否安装好Excel!";
}
int rowCount = source.Rows.Count;//行数
int columnCount = source.Columns.Count;//列数
//保存文化环境
System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Workbook workbook = myExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
worksheet.Name = title;//一个sheet的名称
//myExcel.Visible = true;//打开导出的Excel文件
worksheet.Cells[1, 1] = title;//表标题
//填充列标题
for (int i = 0; i < columnCount; i++)
{
worksheet.Cells[2, i + 1] = source.Columns[i].ColumnName;
}
object[,] objData = new object[rowCount, columnCount];
//填充内容到对象数组
for (int r = 0; r < rowCount; r++)
{
for (int col = 0; col < columnCount; col++)
{
objData[r, col] = source.Rows[r][col].ToString();
}
}
//将对象数组的值赋给Excel对象
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 2, columnCount]);
range.NumberFormat = "@";//设置数字文本格式
range.Value2 = objData;
//设置格式
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).MergeCells = true;//合并单元格
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).RowHeight = 38;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Name = "黑体";
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Size = 16;
//worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 2, columnCount]).Borders.LineStyle = 1;//设置边框
//worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount, columnCount]).Columns.AutoFit();//设置单元格宽度为自适应
try
{
myExcel.Application.Workbooks[1].SaveAs(savePath + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return "恭喜,数据已经成功导出为Excel文件!";
}
catch (Exception ex)
{
return ex.ToString();
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
source.Dispose();
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
GC.Collect();
}
}