转自:http://blog.csdn.net/happy09li/article/details/7431967
三种读取Excel方法
---------------------------------------------------方法一
- /// <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())可以直接用第一行作为列名,单合并单元格后,读取出来后列名相同会报错,所以最好不用
- // 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("数据绑定Excel失败! 失败原因:"+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return dt;
- }
- }
- /// <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("数据绑定Excel失败! 失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
- return null;
- }
- }
——————————————————插入Excel
- /// <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$] (工号,姓名,部门,职务,日期,时间) 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发生错误:"+ex.Message );
- }
- return false;
- }
- //新增、修改
- _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();
- ——————————————————修改Excel的值
- //修改第一行Name的值为张三
- string strComm = "update [Sheet1$] set Name='张三' WHERE 工号='132'";
- OleDbConnection myConn = new OleDbConnection(strConn);
- myConn.Open();
- OleDbCommand com = new OleDbCommand(strComm, myConn);
- com.ExecuteNonQuery();
- myConn.Close();
方法三 用NPOI读取
----------------------------用流导出Excel
- <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>
----------------------------导出
- using _Excel = Microsoft.Office.Interop.Excel;
- using System.Drawing;
- using System.Reflection;
- using System.Windows.Forms;
- using Microsoft.Office.Interop.Excel;
- /// <summary>
- /// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置
- /// </summary>
- /// <param name="dt">要导出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无法启动");
- }
- 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列合并为一个单元格
- range.WrapText = true; //自动换行
- range.EntireRow.AutoFit();//行高根据内容自动调整
- sheet.get_Range("A1", "C3").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = BorderStyle.FixedSingle;//将1-3行和1-3列合并的单元格划下划线,根据[]内属性,可以设置文本上、下、左、右的的边框框
- sheet.get_Range("A1", "C3").Borders.LineStyle = BorderStyle.FixedSingle;//给整个合并的单元格加上边框
- sheet.get_Range("A1", "C3").Font.Name = "Times New Roman";//设置字体.
- sheet.get_Range("A1", "C3").Font.Size = 22;//设置字体大小
- range.Font.Bold = true;//加粗
- range.RowHeight = 22;//调行高
- 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列合并为一个单元格
- range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//合并后文本水平居中
- range.VerticalAlignment = XlVAlign.xlVAlignCenter;//合并后文本竖直居中
- 單個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"; //设置单元格格式为货币格式
- 跨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();//给合并的列赋值
- //循环加载数据
- 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++;
- }
- }
- Range ran = (Range)sheet.Cells[1, 1];//Excel导出加下拉框
- 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();//设置单元格背景颜色
- sheet.Columns.AutoFit();
- sheet.Cells.EntireColumn.AutoFit();
- sheet.Columns.EntireColumn.AutoFit();//列宽自适应。
- // sheet.Cells.Borders.LineStyle =BorderStyle.Fixed3D;//设置边框
- sheet = null;
- book.Save();
- }
- book.Close(sheet, savePath, System.Type.Missing);
- app.Quit();
- app.DisplayAlerts=false; //保存Excel的时候,不弹出是否保存的窗口直接进行保存
- System.GC.Collect();
- KillProcess(app);
- /// <summary>
- /// 导出Excel后,杀死Excel进程
- /// </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);
- C#导出写保护的Excel,设置某些区域可以编辑
- 对Excel操作时,由于使用权限的不同,可能对表格的操作权限也不一样。EXCEL提供了保护工作表以及允许编辑单元格功能。相应的在C#中就可以对Excel表格进行操作。
- 有两种方法可以实现:
- 第一种:
- 主要用Protect()方法保护工作表,Worksheet.Protection.AllowEditRanges设置允许编辑的单元格。
- public void CreateExcel()
- {
- //创建一个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] = "普通报表";
- //逐行写入数据
- for (int i = 0; i < 11; i++)
- {
- for (int j = 0; j < 7; j++)
- {
- //以单引号开头,表示该单元格为纯文本
- 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;
- //设定允许操作的单元格
- Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
- ranges.Add("Information", myExcel.Application.get_Range("B2", "B2"), Type.Missing);
- //保护工作表
- 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:借用此方法我写了个循环来设定单元格保护,没想到一直在报HRESULT:0x800A03EC 的一个异常,郁闷。
- 经过一番折腾,发现 AllowEditRanges.Add方法的第一个参数名是不能够重复的,写循环的时候没注意。
- 第二种:
- 用locked属性,设置Locked = false 的区域就可编辑的区域
- worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[10, 10]).Locked = false;
- //保护工作表
- 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);
- ///
- /// 在工作表中插入行,并调整其他行以留出空间
- ///
- ///
- 当前工作表
- ///
- 欲插入的行索引
- private void InsertRows(Excel.Worksheet sheet, int rowIndex)
- {
- range = (Excel.Range)sheet.Rows[rowIndex, missing];
- //object Range.Insert(object shift, object copyorigin);
- //shift: Variant类型,可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一:
- //xlShiftToRight 或 xlShiftDown。如果省略该参数,Microsoft Excel 将根据区域形状确定调整方式。
- range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
- }
- ///
- /// 在工作表中删除行
- ///
- ///
- 当前工作表
- ///
- 欲删除的行索引
- private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
- {
- range = (Range)sheet.Rows[rowIndex, missing];
- range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
- }
- ///
- /// 退出Excel,并且释放调用的COM资源
- ///
- private void Dispose()
- {
- book.Close(missing, missing, missing);
- app.Workbooks.Close();
- app.Quit();
- <span style="white-space:pre"> </span>}
- ----------------------------NOPI导出Excel-------------------------------
- 改单元格样式点击打开链接
- <a target="_blank" href="http://tonyqus.sinaapp.com/tutorial">NPOI教程</a>
- dll下载地址:<a target="_blank" href="http://download.csdn.net/detail/happy09li/4436160">点击打开链接</a>
- using NPOI;
- using NPOI.HPSF;
- using NPOI.HSSF;
- using NPOI.HSSF.UserModel;
- public static void exportExcel(DataTable dtSource, string strFileName)
- {
- HSSFWorkbook workbook = new HSSFWorkbook();
- HSSFSheet sheet = workbook.CreateSheet();
- //填充表头
- HSSFRow dataRow = sheet.CreateRow(0);
- HSSFCellStyle headStyle = workbook.CreateCellStyle();
- headStyle.Alignment = CellHorizontalAlignment.CENTER;
- HSSFFont font = workbook.CreateFont();
- font.FontHeightInPoints = 20;
- font.Boldweight = 700;
- headStyle.SetFont(font);
- foreach (DataColumn column in dtSource.Columns)
- {
- dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
- dataRow.GetCell(column.Ordinal).CellStyle = headStyle;
- // dataRow.GetCell(column.Ordinal).c
- //设置列宽
- //sheet.SetColumnWidth(column.Ordinal,column.Caption.Length*20);
- }
- //填充内容
- for (int i = 0; i < dtSource.Rows.Count; i++)
- {
- dataRow = sheet.CreateRow(i + 1);
- for (int j = 0; j < dtSource.Columns.Count; j++)
- {
- dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
- }
- }</pre><pre name="code" class="csharp"> for (int m = 0; m < dtData.Columns.Count - 1; m++)
- {
- for (int n = 0; n < dtData.Rows.Count - 1; n++)
- {
- if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n + 1).GetCell(m).ToString() && !string.IsNullOrEmpty(sheet.GetRow(n).GetCell(m).ToString()))
- {
- sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(n, m, n + 1, m));
- }
- //else if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n).GetCell(m + 1).ToString())
- //{
- // sheet.AddMergedRegion(new Region(n, m, n, m + 1));
- //}
- }
- }
- //保存
- using (MemoryStream ms = new MemoryStream())
- {
- using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
- {
- workbook.Write(fs);
- }
- }
- workbook.Dispose();
- }
- <span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space: normal;">
- </span></span>