电子表格求代码: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
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;
}