关于ASP.NET 将数据导出成Excel 的总结[补充]

2024-02-19 04:08•Android•阅读 369

关于ASP.NET 将数据导出成Excel 的总结[补充]

//打开一个空的Excel

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

app.Workbooks.Add(true);

app.Visible = true;

//打开一个已经存在的Excel文件

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

app.Workbooks.Add(路径和文件名);

app.Visible = true;

//读取Excel2003

conExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

//读取Excel2007

conExcel.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +filepath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";

//设置禁止弹出保存和覆盖的询问提示框

app.DisplayAlerts = false;

app.AlertBeforeOverwriting = false;

//复制到指定的地方,默认路径为我的文档

wb.SaveCopyAs(@"D:\111.xlsx");

//新建一个Excel并保存到指定的路径

app = new Microsoft.Office.Interop.Excel.Application();

wb = app.Workbooks.Add(true);

app.Cells[1, 1] = "test";

app.DisplayAlerts = false;

app.AlertBeforeOverwriting = false;

wb.SaveCopyAs(@"C:\111.xlsx");

Close();

//直接读取Excel文件

DataTable myT=ExcelToDataTable("D:/文件/新武昌站点资料.xls","sheet1");

String mystr=myT.Rows[0][0].ToString();

this.textBox1.Text=mystr;

//版本检测

string strPathResult = "";

string strVersionResult = "";

string strKeyName = "Path";

object objResult = null;

Microsoft.Win32.RegistryValueKind regValueKind;

Microsoft.Win32.RegistryKey regKey = null;

Microsoft.Win32.RegistryKey regSubKey = null;

try

{

regKey = Microsoft.Win32.Registry.LocalMachine;

if (regSubKey == null)

{//office97

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\8.0\Common\InstallRoot", false);

strVersionResult = "office97";

strKeyName = "OfficeBin";

}

if (regSubKey == null)

{//Office2000

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\9.0\Common\InstallRoot", false);

strVersionResult = "office2000";

strKeyName = "Path";

}

if (regSubKey == null)

{//officeXp

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\10.0\Common\InstallRoot", false);

strVersionResult = "officeXP";

strKeyName = "Path";

}

if (regSubKey == null)

{//Office2003

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot", false);

strVersionResult = "office2003";

strKeyName = "Path";

}

if (regSubKey == null)

{//office2007

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot", false);

strVersionResult = "office2007";

strKeyName = "Path";

}

objResult = regSubKey.GetValue(strKeyName);

regValueKind = regSubKey.GetValueKind(strKeyName);

if (regValueKind == Microsoft.Win32.RegistryValueKind.String)

{

strPathResult = objResult.ToString();

}

catch (System.Security.SecurityException ex)

{

throw new System.Security.SecurityException("您没有读取注册表的权限", ex);

}

catch (Exception ex)

{

throw new Exception("读取注册表出错!", ex);

}

finally

{

if (regKey != null)

{

regKey.Close();

regKey = null;

}

if (regSubKey != null)

{

regSubKey.Close();

regSubKey = null;

}

MessageBox.Show(strVersionResult);

//获取行数

app = new Microsoft.Office.Interop.Excel.Application();

wb = app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

//行数

string count = ws.UsedRange.Rows.Count.ToString();

//读取某个单元格信息

Range r = (Range)ws.Cells[1, 1];

MessageBox.Show(r.Value2.ToString());

//删除一行数据(第一个单元格的位置)

public void DelRow(int row,string fPath)

{

OpenApp(fPath);

Range range = ws.UsedRange;

int columns = range.Columns.Count;

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

{

((Range)ws.Cells[row, i+1]).Delete(Missing.Value);

}

wb.Save();

Close();

}

//删除一列

public void DelCol(int col, string fPath)

{

OpenApp(fPath);

Range range = ws.UsedRange;

int rows = range.Rows.Count;

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

{

((Range)ws.Cells[1, col]).Delete(Missing.Value);

}

wb.Save();

Close();

}

//删除单元格

public void DelCell(int row,int col,string fPath)

{

OpenApp(fPath);

Range range = (Range)ws.Cells[row, col];

range.Delete(Missing.Value);

wb.Save();

Close();

}

在这里需要注意一下,在Excel表中,删除和清除是不一样的,清除(Clear)只是把内容清空,而单元格还在,删除(Del)是把一个单元格删除,下面的数据就会自动的对其,就会造成排版问题,这是需要注意的

//释放资源

因为引用的是COM组件,是非托管的代码,所以释放资源的代码就需要手动的编写,在网上查找了很多的代码都不好用,下面这个是比较好的。

public void Close()

{

if (app == null) return;

if (wb != null)

{

wb.Close(false, Missing.Value, Missing.Value);

ReleaseCom(wb);

wb = null;

}

this.app.Quit();

ReleaseCom(app);

app = null;

GC.Collect();

}

static private void ReleaseCom(object o)

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象

}

catch { }

finally

{

o = null;

}

//合并单元格

{

ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);

}

public void UniteCells(string ws, int x1, int y1, int x2, int y2)

//合并单元格

{

GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

}

//Range可以获取一个单元格,也可以是多个单元格

获取一个

Range ran=(Range)ws.Cells[Row,Col];

获取多行

Range ran=ws.Get_Range(左上角单元格,右下角单元格);

Range ran=ws.Get_Range(ws.Cells[row,col],ws.Cells[row,Col]);

//设置格式

Range ra = (Range)ws.Cells[row + row + 4, col];

ra.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

ra.NumberFormatLocal = "#,##0.00";

//拷贝格式将ran选中的区域复制到ra选中的区域中

Range ran = ws.get_Range(ws.Cells[3,1],ws.Cells[4,9]);

Range ra = ws.get_Range(ws.Cells[5,1],ws.Cells[6,9]);

ran.Copy(ra);

作者： XuGang 网名：钢钢

出处： http://xugang.cnblogs.com

声明：本文版权归作者和博客园共有！转载时必须保留此段声明，且在文章页面明显位置给出原文连接。

DotNET

//打开一个空的Excel

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

app.Workbooks.Add(true);

app.Visible = true;

//打开一个已经存在的Excel文件

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

app.Workbooks.Add(路径和文件名);

app.Visible = true;

//读取Excel2003

conExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

//读取Excel2007

conExcel.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +filepath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";

//设置禁止弹出保存和覆盖的询问提示框

app.DisplayAlerts = false;

app.AlertBeforeOverwriting = false;

//复制到指定的地方,默认路径为我的文档

wb.SaveCopyAs(@"D:\111.xlsx");

//新建一个Excel并保存到指定的路径

app = new Microsoft.Office.Interop.Excel.Application();

wb = app.Workbooks.Add(true);

app.Cells[1, 1] = "test";

app.DisplayAlerts = false;

app.AlertBeforeOverwriting = false;

wb.SaveCopyAs(@"C:\111.xlsx");

Close();

//直接读取Excel文件

DataTable myT=ExcelToDataTable("D:/文件/新武昌站点资料.xls","sheet1");

String mystr=myT.Rows[0][0].ToString();

this.textBox1.Text=mystr;

//版本检测

string strPathResult = "";

string strVersionResult = "";

string strKeyName = "Path";

object objResult = null;

Microsoft.Win32.RegistryValueKind regValueKind;

Microsoft.Win32.RegistryKey regKey = null;

Microsoft.Win32.RegistryKey regSubKey = null;

try

{

regKey = Microsoft.Win32.Registry.LocalMachine;

if (regSubKey == null)

{//office97

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\8.0\Common\InstallRoot", false);

strVersionResult = "office97";

strKeyName = "OfficeBin";

}

if (regSubKey == null)

{//Office2000

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\9.0\Common\InstallRoot", false);

strVersionResult = "office2000";

strKeyName = "Path";

}

if (regSubKey == null)

{//officeXp

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\10.0\Common\InstallRoot", false);

strVersionResult = "officeXP";

strKeyName = "Path";

}

if (regSubKey == null)

{//Office2003

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot", false);

strVersionResult = "office2003";

strKeyName = "Path";

}

if (regSubKey == null)

{//office2007

regSubKey = regKey.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot", false);

strVersionResult = "office2007";

strKeyName = "Path";

}

objResult = regSubKey.GetValue(strKeyName);

regValueKind = regSubKey.GetValueKind(strKeyName);

if (regValueKind == Microsoft.Win32.RegistryValueKind.String)

{

strPathResult = objResult.ToString();

}

catch (System.Security.SecurityException ex)

{

throw new System.Security.SecurityException("您没有读取注册表的权限", ex);

}

catch (Exception ex)

{

throw new Exception("读取注册表出错!", ex);

}

finally

{

if (regKey != null)

{

regKey.Close();

regKey = null;

}

if (regSubKey != null)

{

regSubKey.Close();

regSubKey = null;

}

MessageBox.Show(strVersionResult);

//获取行数

app = new Microsoft.Office.Interop.Excel.Application();

wb = app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value

, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

//行数

string count = ws.UsedRange.Rows.Count.ToString();

//读取某个单元格信息

Range r = (Range)ws.Cells[1, 1];

MessageBox.Show(r.Value2.ToString());

//删除一行数据(第一个单元格的位置)

public void DelRow(int row,string fPath)

{

OpenApp(fPath);

Range range = ws.UsedRange;

int columns = range.Columns.Count;

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

{

((Range)ws.Cells[row, i+1]).Delete(Missing.Value);

}

wb.Save();

Close();

}

//删除一列

public void DelCol(int col, string fPath)

{

OpenApp(fPath);

Range range = ws.UsedRange;

int rows = range.Rows.Count;

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

{

((Range)ws.Cells[1, col]).Delete(Missing.Value);

}

wb.Save();

Close();

}

//删除单元格

public void DelCell(int row,int col,string fPath)

{

OpenApp(fPath);

Range range = (Range)ws.Cells[row, col];

range.Delete(Missing.Value);

wb.Save();

Close();

}

//释放资源

因为引用的是COM组件,是非托管的代码,所以释放资源的代码就需要手动的编写,在网上查找了很多的代码都不好用,下面这个是比较好的。

public void Close()

{

if (app == null) return;

if (wb != null)

{

wb.Close(false, Missing.Value, Missing.Value);

ReleaseCom(wb);

wb = null;

}

this.app.Quit();

ReleaseCom(app);

app = null;

GC.Collect();

}

static private void ReleaseCom(object o)

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象

}

catch { }

finally

{

o = null;

}

//合并单元格

{

ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);

}

public void UniteCells(string ws, int x1, int y1, int x2, int y2)

//合并单元格

{

GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

}

//Range可以获取一个单元格,也可以是多个单元格

获取一个

Range ran=(Range)ws.Cells[Row,Col];

获取多行

Range ran=ws.Get_Range(左上角单元格,右下角单元格);

Range ran=ws.Get_Range(ws.Cells[row,col],ws.Cells[row,Col]);

//设置格式

Range ra = (Range)ws.Cells[row + row + 4, col];

ra.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

ra.NumberFormatLocal = "#,##0.00";

//拷贝格式将ran选中的区域复制到ra选中的区域中

Range ran = ws.get_Range(ws.Cells[3,1],ws.Cells[4,9]);

Range ra = ws.get_Range(ws.Cells[5,1],ws.Cells[6,9]);

ran.Copy(ra);

上一篇 »【转】Python将列表数据写入文件，txt, csv，excel
下一篇 »使用Python实现将多表分批次从数据库导出到Excel

关于ASP.NET 将数据导出成Excel 的总结[补充]

关于ASP.NET 将数据导出成Excel 的总结[补充]

相关推荐

Python-将数据表中数据导出到excel

Java POI将数据库表查询结果导出到Excel实现及乱码问题

java导出excel并且压缩成zip上传到oss，并下载，使用字节流去存储，不用文件流保存文件到本地

jQuery 数据表： 将单个列导出到 Excel

VB.net 将dataGridView中的数据导出到excel

asp.net中将DataGrid数据导出到excel或word文件中

ASP.NET导出EXCEL 数字转文本格式

asp.net利用剪切板导出excel

jQuery 数据表：将单个列导出到 Excel