电镐,青春期撞上更年期2下载,开心超人第二部
导出至excel是非常常见，我们可以用很多类库，例如aspose、nopi、interop，在这里我们使用微软自家的工具。我的wpf绑定的observablecollection<t>集合。
我要评论 public string excelexport(system.data.datatable dt, string title)
{
try
{
//创建excel
microsoft.office.interop.excel.application excelapp = new microsoft.office.interop.excel.application();
microsoft.office.interop.excel.workbook excelbook = excelapp.workbooks.add(system.type.missing);
//创建工作表（即excel里的子表sheet） 1表示在子表sheet1里进行数据导出
microsoft.office.interop.excel.worksheet excelsheet = (microsoft.office.interop.excel.worksheet)excelbook.worksheets[1];
//如果数据中存在数字类型 可以让它变文本格式显示
excelsheet.cells.numberformat = "@";
//设置工作表名
excelsheet.name = title;
//设置sheet标题
string start = "a1";
string end = changeasc(dt.columns.count) + "1";
microsoft.office.interop.excel.range _range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
_range.merge(0); //单元格合并动作(要配合上面的get_range()进行设计)
_range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
_range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter;
_range.font.size = 22; //设置字体大小
_range.font.name = "宋体"; //设置字体的种类
excelsheet.cells[1, 1] = title; //excel单元格赋值
_range.entirecolumn.autofit(); //自动调整列宽
//写表头
for (int m = 1; m <= dt.columns.count; m++)
{
excelsheet.cells[2, m] = dt.columns[m - 1].columnname.tostring();
start = "a2";
end = changeasc(dt.columns.count) + "2";
_range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
_range.font.size = 14; //设置字体大小
_range.font.name = "宋体"; //设置字体的种类
_range.entirecolumn.autofit(); //自动调整列宽
_range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter;
}
//写数据
for (int i = 0; i < dt.rows.count; i++)
{
for (int j = 1; j <= dt.columns.count; j++)
{
//excel单元格第一个从索引1开始
// if (j == 0) j = 1;
excelsheet.cells[i + 3, j] = dt.rows[i][j - 1].tostring();
}
}
//表格属性设置
for (int n = 0; n < dt.rows.count + 1; n++)
{
start = "a" + (n + 3).tostring();
end = changeasc(dt.columns.count) + (n + 3).tostring();
//获取excel多个单元格区域
_range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
_range.font.size = 12; //设置字体大小
_range.font.name = "宋体"; //设置字体的种类
_range.entirecolumn.autofit(); //自动调整列宽
_range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter; //设置字体在单元格内的对其方式 _range.entirecolumn.autofit(); //自动调整列宽
}
excelapp.displayalerts = false; //保存excel的时候，不弹出是否保存的窗口直接进行保存
////弹出保存对话框,并保存文件
microsoft.win32.savefiledialog sfd = new microsoft.win32.savefiledialog();
sfd.defaultext = ".xlsx";
sfd.filter = "office 2007 file|*.xlsx|office 2000-2003 file|*.xls|所有文件|*.*";
if (sfd.showdialog() == true)
{
if (sfd.filename != "")
{
excelbook.saveas(sfd.filename); //将其进行保存到指定的路径
system.windows.messagebox.show("导出文件已存储为: " + sfd.filename, "温馨提示");
}
}
//释放可能还没释放的进程
excelbook.close();
excelapp.quit();
return sfd.filename;
}
catch
{
//system.windows.messagebox.show("导出文件保存失败,可能原因该文件已打开！", "警告！");
return null;
}
}
该方法还不止这么多，还涉及到了1-27 = a-z 的适配，如以下方法。
/// <summary>
/// 获取当前列列名,并得到excel中对应的列
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
private string changeasc(int count)
{
string ascstr = "";
switch (count)
{
case 1:
ascstr = "a";
break;
case 2:
ascstr = "b";
break;
case 3:
ascstr = "c";
break;
case 4:
ascstr = "d";
break;
case 5:
ascstr = "e";
break;
case 6:
ascstr = "f";
break;
case 7:
ascstr = "g";
break;
case 8:
ascstr = "h";
break;
case 9:
ascstr = "i";
break;
case 10:
ascstr = "j";
break;
case 11:
ascstr = "k";
break;
case 12:
ascstr = "l";
break;
case 13:
ascstr = "m";
break;
case 14:
ascstr = "n";
break;
case 15:
ascstr = "o";
break;
case 16:
ascstr = "p";
break;
case 17:
ascstr = "q";
break;
case 18:
ascstr = "r";
break;
case 19:
ascstr = "s";
break;
case 20:
ascstr = "t";
break;
default:
ascstr = "u";
break;
}
return ascstr;
}
最后我们获取数据给方法，下面是设计了数据转换到datatable的一个过程。
private void export(object sender, routedeventargs e)
{
datatable newtb = new datatable();
observablecollection<xmodel.store_in> instore = this.datagrid.itemssource as observablecollection<xmodel.store_in>;
list<xmodel.store_in> list = new list<xmodel.store_in>(instore.tolist());
newtb = xtools.xhelper.datavalidation.copytodatatable<xmodel.store_in>(list);
excelexport(newtb, "入库记录");
}
public static class datavalidation
{
public static datatable copytodatatable<t>(this ienumerable<t> array)
{
var ret = new datatable();
foreach (propertydescriptor dp in typedescriptor.getproperties(typeof(t)))
ret.columns.add(dp.name);
foreach (t item in array)
{
var row = ret.newrow();
foreach (propertydescriptor dp in typedescriptor.getproperties(typeof(t)))
row[dp.name] = dp.getvalue(item);
ret.rows.add(row);
}
return ret;
}
}
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Blazor server side 自家的一些开源的, 实用型项目的进度之 CEF客户端
.NET IoC模式依赖反转(DIP)、控制反转(Ioc)、依赖注入(DI)
vue+.netcore可支持业务代码扩展的开发框架 VOL.Vue 2.0版本发布
网友评论