Question

如何在C#中写Excel文件

Answer 1

C#创建Excel文档，下面的代码需要添加spire.doc.dll为依赖

1. //创建一个workbook实例

2. Workbook wb = new Workbook();

3. 
   

4. //清除默认的工作表

5. wb.Worksheets.Clear();

6. 
   

7. //添加一个工作表并指定表名

8. Worksheet sheet = wb.Worksheets.Add("职员表");

9. 
   

10. //横向合并A1到G1的单元格

11. sheet.Range["A1:G1"].Merge();

12. 
    

13. //写入数据到A1单元格，设置文字格式及对齐方式

14. sheet.Range["A1"].Value = "环宇汽车公司员工基本情况登记表"; 

15. sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;

16. sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;

17. sheet.Range["A1"].Style.Font.IsBold = true;

18. sheet.Range["A1"].Style.Font.Size = 13F;

19. 
    

20. //设置第一行行高

21. sheet.Rows[0].RowHeight = 30F;

22. 
    

23. //创建一个DataTable

24. DataTable dt = new DataTable();

25. dt.Columns.Add("姓名");

26. dt.Columns.Add("性别");

27. dt.Columns.Add("出生日期");

28. dt.Columns.Add("学历");

29. dt.Columns.Add("联系电话");

30. dt.Columns.Add("职务");

31. dt.Columns.Add("工号");

32. dt.Rows.Add("王伟", "男", "1990年2月10日", "本科", "13524756854", "销售","0054");

33. dt.Rows.Add("李宁", "男", "1985年6月8日", "大专", "13259863247", "销售","0055");

34. dt.Rows.Add("邓家佳", "女", "1989年11月25日", "本科", "13601540352", "销售","0029");

35. dt.Rows.Add("杜平安", "男", "1978年4月16日", "中专", "13352014060", "保安","0036");

36. dt.Rows.Add("唐静", "女", "1980年1月21日", "本科", "13635401489", "店长","0010");

37. 
    

38. //将DataTable数据写入工作表

39. sheet.InsertDataTable(dt, true, 2, 1, true);

40. 
    

41. //设置该区域行高

42. sheet.Range["A2:G7"].RowHeight = 15F;

43. 
    

44. //设置第三列及第五列列宽

45. sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;

46. sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;

47. 
    

48. //设置网格线样式及颜色

49. sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);   

50. sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);

51. sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);

52. sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;

53. 
    

54. //保存为.xlsx文件

55. wb.SaveToFile("创建Excel.xlsx", FileFormat.Version2013);

Answer 2

1. 创建一个Console Application, 添加COM选显卡中的Microsoft Excel 12.0 Object Library引用.
2. 粘贴下面的源代码.
using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace CShartExcelTest
{
class Program
{
static void Main(string[] args)
{
try
{
string[,] values = {
{"Tom", "18", "Beijing", "13912345678"},
{"Jerry", "17", "Shanghai", "13687654321"}
};
Excel.Application objApp;
Excel._Workbook objBook;
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;

// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add(System.Reflection.Missing.Value);
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);

//Get the range where the starting cell has the address
range = objSheet.get_Range("A2", System.Reflection.Missing.Value);
range = range.get_Resize(2, 4);
range.set_Value(System.Reflection.Missing.Value, values);

//Construct the header.
objSheet.Cells[1, 1] = "Name";
objSheet.Cells[1, 2] = "Age";
objSheet.Cells[1, 3] = "HomeTown";
objSheet.Cells[1, 4] = "Mobile";

bool bSave = false;

if (bSave)
{
//Return control of Excel to the user.
objApp.Visible = true;
objApp.UserControl = true;
}
else
{
objBook.SaveAs(@"c:\temp\csharp-Excel.xls",
Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);

objBook.Close(true,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
objApp.Quit();

releaseObject(objSheet);
releaseObject(objBook);
releaseObject(objApp);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

}
}