C#创建Excel(.xls和.xlsx)文件的三种方法

2023-08-09 23:19:40

生成EXCEL文件是经常需要用到的功能，我们利用一些开源库可以很容易实现这个功能。

方法一：利用excellibrary，http://code.google.com/p/excellibrary/

excellibrary是国人写的开源组件，很容易使用，可惜貌似还不支持.xlsx(Excel 2007)，例子如下：

//Create the data set and table

DataSet ds = new DataSet("New_DataSet");

DataTable dt = new DataTable("New_DataTable");

//Set the locale for each

ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)

OleDbConnection con = new OleDbConnection(dbConnectionString);

con.Open();

//Create a query and fill the data table with the data from the DB

string sql = "SELECT Whatever FROM MyDBTable;";

OleDbCommand cmd = new OleDbCommand(sql, con);

OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;

adptr.Fill(dt);

con.Close();

//Add the table to the data set

ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set

ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

例子二：

//create new xls file

string file = "C:\\newdoc.xls";

Workbook workbook = new Workbook();

Worksheet worksheet = new Worksheet("First Sheet");

worksheet.Cells[, ] = new Cell((short));

worksheet.Cells[, ] = new Cell();

worksheet.Cells[, ] = new Cell((decimal)3.45);

worksheet.Cells[, ] = new Cell("Text string");

worksheet.Cells[, ] = new Cell("Second string");

worksheet.Cells[, ] = new Cell(32764.5, "#,##0.00");

worksheet.Cells[, ] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");

worksheet.Cells.ColumnWidth[, ] = ;

workbook.Worksheets.Add(worksheet);

workbook.Save(file);

// open xls file

Workbook book = Workbook.Load(file);

Worksheet sheet = book.Worksheets[];

// traverse cells

foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)

{

dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;

}

// traverse rows by Index

for (int rowIndex = sheet.Cells.FirstRowIndex;

rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)

{

Row row = sheet.Cells.GetRow(rowIndex);

for (int colIndex = row.FirstColIndex;

colIndex <= row.LastColIndex; colIndex++)

{

Cell cell = row.GetCell(colIndex);

}

}

方法二：利用EPPlus,http://epplus.codeplex.com/
EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件。
例子如下：

var file = @"Sample.xlsx";

if (File.Exists(file)) File.Delete(file);

using (var excel = new ExcelPackage(new FileInfo(file)))

{

var ws = excel.Workbook.Worksheets.Add("Sheet1");

ws.Cells[1, 1].Value = "Date";

ws.Cells[1, 2].Value = "Price";

ws.Cells[1, 3].Value = "Volume";

var random = new Random();

for (int i = 0; i < 10; i++)

{

ws.Cells[i + 2, 1].Value = DateTime.Today.AddDays(i);

ws.Cells[i + 2, 2].Value = random.NextDouble() * 1e3;

ws.Cells[i + 2, 3].Value = random.Next() / 1e3;

}

ws.Cells[2, 1, 11, 1].Style.Numberformat.Format = "dd/MM/yyyy";

ws.Cells[2, 2, 11, 2].Style.Numberformat.Format = "#,##0.000000";

ws.Cells[2, 3, 11, 3].Style.Numberformat.Format = "#,##0";

ws.Column(1).AutoFit();

ws.Column(2).AutoFit();

ws.Column(3).AutoFit();

excel.Save();

}

例子二：

using OfficeOpenXml;

//指定Templete文档Text.xlsx

FileInfo newFile = new FileInfo("D:" + @"\Test.xlsx");

//开启

using (ExcelPackage pck = new ExcelPackage(newFile))

{

try

{

//设定ExcelWorkBook

ExcelWorkbook workBook = pck.Workbook;

if (workBook != null)

{

if (workBook.Worksheets.Count > 0)

{

//复制Temp这个Sheet同时命名为《清单》

ExcelWorksheet currentWorksheet = workBook.Worksheets.Copy("Temp", "清单");

//可以设定保护Sheet的密码

//currentWorksheet.Protection.SetPassword("1234");

int StartRow = 4;

for (int i = 0; i < tDS.Tables[0].Rows.Count; i++)

{

//Cells[RowIndex,CellIndex]

currentWorksheet.Cells[StartRow + i, 1].Value = Convert.ToString(tDS.Tables[0].Rows[i][0]);

currentWorksheet.Cells[StartRow + i, 2].Value = Convert.ToString(tDS.Tables[0].Rows[i][1]);

currentWorksheet.Cells[StartRow + i, 3].Value = Convert.ToString(tDS.Tables[0].Rows[i][2]);

currentWorksheet.Cells[StartRow + i, 4].Value = Convert.ToString(tDS.Tables[0].Rows[i][3]);

currentWorksheet.Cells[StartRow + i, 5].Value = Convert.ToString(tDS.Tables[0].Rows[i][4]);

currentWorksheet.Cells[StartRow + i, 6].Value = Convert.ToString(tDS.Tables[0].Rows[i][5]);

currentWorksheet.Cells[StartRow + i, 7].Value = Convert.ToString(tDS.Tables[0].Rows[i][6]);

currentWorksheet.Cells[StartRow + i, 8].Value = Convert.ToString(tDS.Tables[0].Rows[i][7]);

currentWorksheet.Cells[StartRow + i, 9].Value = Convert.ToString(tDS.Tables[0].Rows[i][8]);

currentWorksheet.Cells[StartRow + i, 10].Value = Convert.ToString(tDS.Tables[0].Rows[i][9]);

currentWorksheet.Cells[StartRow + i, 11].Value = Convert.ToString(tDS.Tables[0].Rows[i][10]);

currentWorksheet.Cells[StartRow + i, 12].Value = Convert.ToString(tDS.Tables[0].Rows[i][11]);

currentWorksheet.Cells[StartRow + i, 13].Value = Convert.ToString(tDS.Tables[0].Rows[i][12]);

}

//将Temp 这个Sheet删除

workBook.Worksheets.Delete("Temp");

}

}

//存至Text4.xlsx

pck.SaveAs(new FileInfo("H:" + @"\Test4.xlsx"));

}

catch (Exception e)

{

oLogger.Fatal(e.ToString());

}

}

方法三：NPOI http://npoi.codeplex.com/
NPOI无需Office COM组件且不依赖Office，使用NPOI能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
被人称为操作EXCEL的终极方案，例子如下：

//引用

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.POIFS.FileSystem;

using NPOI.SS.UserModel;

//将WorkBook指到我们原本设计好的Templete Book1.xls

using (IWorkbook wb = new HSSFWorkbook(new FileStream("D:/Book1.xls", FileMode.Open)))

{

try

{

//设定要使用的Sheet为第0个Sheet

ISheet TempSheet = wb.GetSheetAt(0);

int StartRow = 4;

//tDS为Query回来的资料

for (int i = 0; i < tDS.Tables[0].Rows.Count; i++)

{

//第一个Row要用Create的

TempSheet.CreateRow(StartRow + i).CreateCell(0).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][0]));

//第二个Row之后直接用Get的

TempSheet.GetRow(StartRow + i).CreateCell(1).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][1]));

TempSheet.GetRow(StartRow + i).CreateCell(2).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][2]));

TempSheet.GetRow(StartRow + i).CreateCell(3).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][3]));

TempSheet.GetRow(StartRow + i).CreateCell(4).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][4]));

TempSheet.GetRow(StartRow + i).CreateCell(5).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][5]));

TempSheet.GetRow(StartRow + i).CreateCell(6).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][6]));

TempSheet.GetRow(StartRow + i).CreateCell(7).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][7]));

TempSheet.GetRow(StartRow + i).CreateCell(8).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][8]));

TempSheet.GetRow(StartRow + i).CreateCell(9).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][9]));

TempSheet.GetRow(StartRow + i).CreateCell(10).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][10]));

TempSheet.GetRow(StartRow + i).CreateCell(11).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][11]));

TempSheet.GetRow(StartRow + i).CreateCell(12).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][12]));

}

//将文档写到指定位置

using (FileStream file = new FileStream("H:/Test_NPOI4.xls", FileMode.Create))

{

wb.Write(file);

file.Close();

file.Dispose();

}

}

catch (Exception e)

{

string a = e.ToString();

}

}

码农公寓

相关文章