99热精品毛片全部国产无缓冲,国产资源精品中文字幕,人妻精品动漫H无码中字

關于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);

posted on 2012-02-08 00:58 鋼鋼閱讀(1379) 評論(2) 收藏舉報

刷新頁面返回頂部

XuGang

關于ASP.NET 將數據導出成Excel 的總結[補充]

導航

公告