狠狠干中文字幕,成人看的午夜免费毛片,久久久精品免费国产四虎

三種讀取Excel方法

---------------------------------------------------方法一

[csharp] view plain copy print ?

/// <summary>
/// 解析Excel，返回DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ImpExcel(string fileName)
{
System.Data.DataTable dt = new System.Data.DataTable();
try
{
Microsoft.Office.Interop.Excel.Application app;
Workbooks wbs;
Worksheet ws;
app = new Microsoft.Office.Interop.Excel.Application();
wbs = app.Workbooks;
wbs.Add(fileName);
ws = (Worksheet)app.Worksheets.get_Item(1);
int rows = ws.UsedRange.Rows.Count;
int columns = ws.UsedRange.Columns.Count;
string bookName = ws.Name;
dt = LoadDataFromExcel(filePath, bookName).Tables[0];
//for (int i = 1; i < rows + 1; i++)
//{
// DataRow dr = dt.NewRow();
// for (int j = 1; j <= columns; j++)
// {
// _Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);
// range.Select();
// if (i == 1)
// dt.Columns.Add("Columns" + j);// dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作為列名，單合并單元格后，讀取出來后列名相同會(huì)報(bào)錯(cuò)，所以最好不用
// dr[j - 1] = app.ActiveCell.Text.ToString();
// }
// dt.Rows.Add(dr);
//}
//newdt = dt.Clone();
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (dt.Rows[i][5].ToString() != "" && dt.Rows[i][6].ToString() != "" && dt.Rows[i][7].ToString() != "" && dt.Rows[i][8].ToString() != "" || i == 0)
// newdt.ImportRow(dt.Rows[i]);
//}
KillProcess(app);
return dt;
}
catch (Exception ex)
{
MessageBox.Show("數(shù)據(jù)綁定Excel失敗! 失敗原因："+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return dt;
}
}

----------------------------------------------------方法二

[csharp] view plain copy print ?

/// <summary>
/// 解析Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="name"></param>
/// <returns></returns>
public static DataSet LoadDataFromExcel(string filePath, string name)
{
try
{
string strConn;
// strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名稱，比如sheet2，等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, name);
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("數(shù)據(jù)綁定Excel失敗! 失敗原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}

——————————————————插入Excel

[csharp] view plain copy print ?

/// <summary>
/// 寫入Excel文檔
/// </summary>
/// <param name="Path">文件名稱</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
{
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工號(hào),姓名,部門,職務(wù),日期,時(shí)間) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("寫入Excel發(fā)生錯(cuò)誤："+ex.Message );
}
return false;
}

[csharp] view plain copy print ?

//新增、修改
_Excel.Application app = new _Excel.ApplicationClass();
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(UpdateExcelPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.Cells[i + 2, 1] = dt.Rows[i][0].ToString();
sheet.Cells[i + 2, 2] = dt.Rows[i][1].ToString();
}
book.Save();
book.Close(sheet, UpdateExcelPath, System.Type.Missing);
app.Quit();
System.GC.Collect();

[csharp] view plain copy print ?

——————————————————修改Excel的值
//修改第一行Name的值為張三
string strComm = "update [Sheet1$] set Name='張三' WHERE 工號(hào)='132'";
OleDbConnection myConn = new OleDbConnection(strConn);
myConn.Open();
OleDbCommand com = new OleDbCommand(strComm, myConn);
com.ExecuteNonQuery();
myConn.Close();

----------------------------用流導(dǎo)出Excel

[csharp] view plain copy print ?

<span style="font-size:18px;"> private void FileStream()
{
FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
string html = @"C:\Documents and Settings\Administrator\桌面\1.html";
sw.WriteLine(File.ReadAllText(html));
sw.Close();
}</span>

NOPI操作Excel

----------------------------導(dǎo)出

[csharp] view plain copy print ?

using _Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// DataTable直接導(dǎo)出Excel,此方法會(huì)把DataTable的數(shù)據(jù)用Excel打開,再自己手動(dòng)去保存到確切的位置
/// </summary>
/// <param name="dt">要導(dǎo)出Excel的DataTable</param>
/// <returns></returns>
public static void ExportExcel(System.Data.DataTable table, string savePath, bool isExit)
{
if (!isExit)//保存路徑是否存在
File.Copy(System.Windows.Forms.Application.StartupPath + @"\Excel\Excel.xls", savePath);
_Excel.Application app = new _Excel.ApplicationClass();
if (app == null)
{
throw new Exception("Excel無法啟動(dòng)");
}
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
for (int k = 1; k < ds.Tables[1].Rows.Count + 1; k++)
{
_Excel.Sheets xlSheets = book.Sheets as Sheets;
// 添加 Sheet
sheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
}
for (int j = 1; j < ds.Tables[1].Rows.Count + 1; j++)
{
_Excel.Range range = null;
sheet = (_Excel.Worksheet)book.Sheets[j];
if (!istrue)
sheet.Name ="123";
else
sheet.Name = "345";
range = sheet.get_Range("A1", "C3");
range.MergeCells = true;//合并，將1-3行和1-3列合并為一個(gè)單元格
range.WrapText = true; //自動(dòng)換行
range.EntireRow.AutoFit();//行高根據(jù)內(nèi)容自動(dòng)調(diào)整
sheet.get_Range("A1", "C3").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = BorderStyle.FixedSingle;//將1-3行和1-3列合并的單元格劃下劃線，根據(jù)[]內(nèi)屬性，可以設(shè)置文本上、下、左、右的的邊框框
sheet.get_Range("A1", "C3").Borders.LineStyle = BorderStyle.FixedSingle;//給整個(gè)合并的單元格加上邊框
sheet.get_Range("A1", "C3").Font.Name = "Times New Roman";//設(shè)置字體.
sheet.get_Range("A1", "C3").Font.Size = 22;//設(shè)置字體大小
range.Font.Bold = true;//加粗
range.RowHeight = 22;//調(diào)行高
rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//加背景色
range = sheet.get_Range(string.Format("D{0}", 1), string.Format("F{0}", 1));
range.MergeCells = true;//合并，將3-5列合并為一個(gè)單元格
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//合并后文本水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;//合并后文本豎直居中
單個(gè)sheet里求和：
Excel.Range range2 = sheet.get_Range("B25", Type.Missing);
range2.Formula = "=SUM(B2:B24)";
range2.Calculate();
rang = (_Excel.Range)sheet.get_Range(string.Format("G{0}", 7), string.Format("G{0}", table.Rows.Count + 7));
rang.NumberFormatLocal = "$#,##0.00"; //設(shè)置單元格格式為貨幣格式
跨sheet求和：
Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
range3.Formula = "=Sheet3!B8+Sheet3!B12";
range3.Calculate();
sheet.Cells[1, 4] = ds.Tables[1].Rows[0]["FeightCode"].ToString();//給合并的列賦值
//循環(huán)加載數(shù)據(jù)
int startIndex = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[1].Rows[j - 1]["BoxCode"].ToString() == ds.Tables[0].Rows[i]["BoxCode"].ToString())
{
range = sheet.get_Range("b" + (15 + startIndex) + "", "e" + (15 + startIndex) + "");
range.MergeCells = true;
sheet.Cells[15 + startIndex, 2] = ds.Tables[0].Rows[i]["TypeName"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 2];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 6] = ds.Tables[0].Rows[i]["Qty"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 6];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 7] = ds.Tables[0].Rows[i]["UnitPrice"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 7];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
sheet.Cells[15 + startIndex, 8] = ds.Tables[0].Rows[i]["Subtotal"].ToString();
range = (_Excel.Range)sheet.Cells[15 + startIndex, 8];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Bold = true;
range.RowHeight = 22;
Image image = Picture.ReadPicture(ds.Tables[0].Rows[row][col].ToString());
range = sheet.get_Range(string.Format("A{0}", row * num + 29), string.Format("A{0}", row * num + 48));
sheet.Shapes.AddPicture(ds.Tables[0].Rows[row][col].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(range.Left) + 15 + image.Width, Convert.ToSingle(range.Top) + 5, image.Width, image.Height); //插入圖片
range.EntireColumn.AutoFit();
System.Windows.Forms.Application.DoEvents();
startIndex++;
}
}

[csharp] view plain copy print ?

Range ran = (Range)sheet.Cells[1, 1];//Excel導(dǎo)出加下拉框
ran.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "1,2,3", "123");
ran.Validation.InCellDropdown = true;
ran.Validation.IgnoreBlank = true;
ran.Value2 = "2";
rang = sheet.get_Range("a" + (table.Rows.Count + 3) + "", "r" + (table.Rows.Count + 3) + "");
rang.Font.Bold = true;
rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//設(shè)置單元格背景顏色
sheet.Columns.AutoFit();
sheet.Cells.EntireColumn.AutoFit();
sheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
// sheet.Cells.Borders.LineStyle =BorderStyle.Fixed3D;//設(shè)置邊框
sheet = null;
book.Save();
}
book.Close(sheet, savePath, System.Type.Missing);
app.Quit();
app.DisplayAlerts=false; //保存Excel的時(shí)候，不彈出是否保存的窗口直接進(jìn)行保存
System.GC.Collect();
KillProcess(app);
/// <summary>
/// 導(dǎo)出Excel后，殺死Excel進(jìn)程
/// </summary>
/// <param name="app"></param>
private static void KillProcess(_Excel.Application app)
{
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

[csharp] view plain copy print ?

C#導(dǎo)出寫保護(hù)的Excel，設(shè)置某些區(qū)域可以編輯
對(duì)Excel操作時(shí)，由于使用權(quán)限的不同，可能對(duì)表格的操作權(quán)限也不一樣。EXCEL提供了保護(hù)工作表以及允許編輯單元格功能。相應(yīng)的在C#中就可以對(duì)Excel表格進(jìn)行操作。
有兩種方法可以實(shí)現(xiàn)：
第一種：
主要用Protect（）方法保護(hù)工作表，Worksheet.Protection.AllowEditRanges設(shè)置允許編輯的單元格。
public void CreateExcel()
{
//創(chuàng)建一個(gè)Excel文件
Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = null;
myExcel.Application.Workbooks.Add(true);
//讓Excel文件可見
myExcel.Visible = true;
myExcel.Cells[1, 4] = "普通報(bào)表";
//逐行寫入數(shù)據(jù)
for (int i = 0; i < 11; i++)
{
for (int j = 0; j < 7; j++)
{
//以單引號(hào)開頭，表示該單元格為純文本
myExcel.Cells[2 + i, 1 + j] = "'" + i;
}
}
try
{
string excelTemp ="c:\\a.xls";
//excelWorkbook = myExcel.Workbooks[1];
excelWorkbook = myExcel.ActiveWorkbook;
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;
//設(shè)定允許操作的單元格
Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
ranges.Add("Information", myExcel.Application.get_Range("B2", "B2"), Type.Missing);
//保護(hù)工作表
excelSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing);
//Realease the com object
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
excelSheet = null;
//Save the result to a temp path
excelWorkbook.SaveAs(excelTemp, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing,Type.Missing,Type.Missing);
}
catch (Exception ex)
{
throw;
}
finally
{
if (excelWorkbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
}
if (myExcel != null)
{
myExcel.Workbooks.Close();
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myExcel = null;
}
GC.Collect();
}
}
PS：借用此方法我寫了個(gè)循環(huán)來設(shè)定單元格保護(hù)，沒想到一直在報(bào)HRESULT:0x800A03EC 的一個(gè)異常，郁悶。
經(jīng)過一番折騰，發(fā)現(xiàn) AllowEditRanges.Add方法的第一個(gè)參數(shù)名是不能夠重復(fù)的，寫循環(huán)的時(shí)候沒注意。

[csharp] view plain copy print ?

第二種：
用locked屬性，設(shè)置Locked = false 的區(qū)域就可編輯的區(qū)域
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[10, 10]).Locked = false;
//保護(hù)工作表
worksheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, true, true);
///
/// 在工作表中插入行，并調(diào)整其他行以留出空間
///
///
當(dāng)前工作表
///
欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
range = (Excel.Range)sheet.Rows[rowIndex, missing];
//object Range.Insert(object shift, object copyorigin);
//shift: Variant類型，可選。指定單元格的調(diào)整方式?？梢詾橄铝?nbsp; XlInsertShiftDirection 常量之一：
//xlShiftToRight 或 xlShiftDown。如果省略該參數(shù)，Microsoft Excel 將根據(jù)區(qū)域形狀確定調(diào)整方式。
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
}
///
/// 在工作表中刪除行
///
///
當(dāng)前工作表
///
欲刪除的行索引
private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
{
range = (Range)sheet.Rows[rowIndex, missing];
range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
///
/// 退出Excel，并且釋放調(diào)用的COM資源
///
private void Dispose()
{
book.Close(missing, missing, missing);
app.Workbooks.Close();
app.Quit(); }

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊舉報(bào)。

九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区