电子表格求代码:C#中使用Epplus读取Excel的一列数据存到Dictionary

1.求代码:C#中使用Epplus读取Excel的一列数据存到Dictionary

EPPlus 是使用Open Office XML格式（xlsx）读写Excel 2007 / 2010文件的.net开发库。

public void Cells_To_Dictionary_Test()

{

//Throw in some data

var datatable = new DataTable("tblData");

datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });

for (var i = 0; i {

var row = datatable.NewRow();

row[0] = i;

row[1] = i * 10;

row[2] = Path.GetRandomFileName();

datatable.Rows.Add(row);

}

//Create a test file

var existingFile = new FileInfo(@"c:\temp\Grouped.xlsx");

if (existingFile.Exists)

existingFile.Delete();

using (var pck = new ExcelPackage(existingFile))

{

var worksheet = pck.Workbook.Worksheets.Add("Sheet1");

worksheet.Cells.LoadFromDataTable(datatable, true);

pck.Save();

}

//Load a dictionary from a file

using (var pck = new ExcelPackage(existingFile))

{

var worksheet = pck.Workbook.Worksheets["Sheet1"];

//Cells only contains references to cells with actual data

var cells = worksheet.Cells;

var dictionary = cells

.GroupBy(c =>new {c.Start.Row, c.Start.Column})

.ToDictionary(

rcg =>new KeyValuePair(rcg.Key.Row, rcg.Key.Column),

rcg =>cells[rcg.Key.Row, rcg.Key.Column].Value);

foreach (var kvp in dictionary)

Console.WriteLine("{{ Row: {0}, Column: {1}, Value: \"{2}\" }}", kvp.Key.Key, kvp.Key.Value, kvp.Value);

}

}

2.求代码:C#中使用Epplus读取Excel的一列数据存到Dictionary

EPPlus 是使用Open Office XML格式（xlsx）读写Excel 2007 / 2010文件的.net开发库。

public void Cells_To_Dictionary_Test(){ //Throw in some data var datatable = new DataTable("tblData"); datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) }); for (var i = 0; i < 10; i++) { var row = datatable.NewRow(); row[0] = i; row[1] = i * 10; row[2] = Path.GetRandomFileName(); datatable.Rows.Add(row); } //Create a test file var existingFile = new FileInfo(@"c:\temp\Grouped.xlsx"); if (existingFile.Exists) existingFile.Delete(); using (var pck = new ExcelPackage(existingFile)) { var worksheet = pck.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(datatable, true); pck.Save(); } //Load a dictionary from a file using (var pck = new ExcelPackage(existingFile)) { var worksheet = pck.Workbook.Worksheets["Sheet1"]; //Cells only contains references to cells with actual data var cells = worksheet.Cells; var dictionary = cells .GroupBy(c => new {c.Start.Row, c.Start.Column}) .ToDictionary( rcg => new KeyValuePair(rcg.Key.Row, rcg.Key.Column), rcg => cells[rcg.Key.Row, rcg.Key.Column].Value); foreach (var kvp in dictionary) Console.WriteLine("{{ Row: {0}, Column: {1}, Value: \"{2}\" }}", kvp.Key.Key, kvp.Key.Value, kvp.Value); }}。

3.EPPlus使用过程中怎么让导出的excel设置单元格格式

poi或jxl都可以生成excel，给你说下jxl怎么生成的吧，详细的api你可以从网上下载。

//添加带有formatting的Number对象

jxl.write.NumberFormat nf = new jxl.write.NumberFormat（"#.##")； //设置数字格式

jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf)； //设置表单格式

jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN)； //格式化数值

ws.addCell(labelNF)； //在表单中添加格式化的数字

4.EPPlus使用过程中怎么让导出的excel设置单元格格式

poi或jxl都可以生成excel，给你说下jxl怎么生成的吧，详细的api你可以从网上下载。

//添加带有formatting的Number对象

jxl.write.NumberFormat nf = new jxl.write.NumberFormat（"#.##")； //设置数字格式

jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf)； //设置表单格式

jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN)； //格式化数值

ws.addCell(labelNF)； //在表单中添加格式化的数字

5.c#如何使用ExcelPackage Plus读取excel(.xls和xlsx)中得数据

ExcelPackage Plus是ExcelPackage的增强版，它在ExcelPackage的基础上，增强了许多功能包括对流、Linq的支持，可以说相当不错。

它的索引号是从1开始的，主要使用的类型位于OfficeOpenXml空间，具体测试代码如下：

MemoryStream CreateByExcelLibrary(DataTable table)

{

using(ExcelPackage package = newExcelPackage())

{

ExcelWorksheet sheet = package.Workbook.Worksheets.Add("sheet111"); intcolCount = table.Columns.Count;

for(inti = 0; i < colCount; i++)

{

sheet.Cells[1, i + 1].Value = table.Columns[i].Caption;

}

intk = 2;

foreach(DataRow row intable.Rows)

{

for(inti = 0; i < colCount; i++)

{

sheet.Cells[k, i + 1].Value = row[i];

}

k++;

}

MemoryStream ms = newMemoryStream();

package.SaveAs(ms);

returnms;

} }

DataTable ReadByExcelLibrary(Stream xlsStream)

{

DataTable table = newDataTable();

using(ExcelPackage package = newExcelPackage(xlsStream))

{

ExcelWorksheet sheet = package.Workbook.Worksheets[1];

intcolCount = sheet.Dimension.End.Column;

introwCount = sheet.Dimension.End.Row;

for(ushortj = 1; j <= colCount; j++)

{

table.Columns.Add(newDataColumn(sheet.Cells[1, j].Value.ToString()));

}

for(ushorti = 2; i <= rowCount; i++)

{

DataRow row = table.NewRow();

for(ushortj = 1; j <= colCount; j++)

{

row[j - 1] = sheet.Cells[i, j].Value;

}

table.Rows.Add(row);

}

}

returntable;

}