C# Excel 解决方案
因为现在用到的是winform,对web的解决没有去关注,所以现在只是对winform操作excel做笔记
以前做考试系统的时候,接触过excel导入,不过当时不是我负责所以了解不多,只是知道在.net中excel可以想其他数据源一样访问。
C# 操作Excel分两种情况
1.利用office组件,就是要安装office(或者下载dll添加引用也可以,没试过) 。代码1
2.不利用office组件,而是用TextWriter的某些子类编写器将字节流写入文件,这些文件其实不是真正的excel文件(可以用记事本打开不乱码),虽然能用excel打开。如果你在把这些导出的文件当作excel数据源,就不行了。
其实还有一种方法虽然不利用office组件不过当导出到excel的时候有有一个事先创建好的excel文件(这样比用office组件好点,毕竟有别的编译器可以生成excel文件,不过这样也只是自欺欺人吧)。这个方法是导出的时候也把已经创建好的excel当做数据源用OleDbCommand.ExecuteNonQuery()等方法写入excel。我们把这标记为代码3,以便下面提供代码。
相关代码:
我们先看最后一种方法代码(我开始用的是这个不上不下的方法)
代码3
- /// <summary>
- /// 将DataTable导出为excel 自动创建excel
- /// </summary>
- /// <param name="dt"> 数据源</param>
- /// <param name="ExcelFileName"> 要保存的excel的name</param>
- /// <param name="strWorkSheetName">创建的表的名字 </param>
- public static string ExportTable2ExcelFile(DataTable dt, string ExcelFileName, string strWorkSheetName)
- {
- if (File.Exists(ExcelFileName) == false)
- {
- return "指定文件不存在!";
- }
- if (dt == null)
- {
- return "数据不能为空!";
- }
- if (strWorkSheetName.ToString() == "")
- {
- return "数据表名不可以为空!";
- }
- dt.TableName = strWorkSheetName;
- int iRows = dt.Rows.Count;
- int iCols = dt.Columns.Count;
- StringBuilder stringBuilder;
- string connString;
- if (iRows == 0)
- {
- return "没有可导入数据!";
- }
- stringBuilder = new StringBuilder();
- connString = ExcelOperation.GetExcelConnection(ExcelFileName);// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";
- //先查看此Excel中是否有相关Table,如果有的话就删除,然后导入新的。
- //生成创建表的脚本
- stringBuilder.Append("CREATE TABLE ");
- stringBuilder.Append(dt.TableName + " ( ");
- for (int i = 0; i < iCols; i++)
- {
- //此处是本版本改进中最实用的地方
- string strType = ExcelOperation.GetOleDataType(dt.Columns[i]);
- if (i < iCols - 1)
- stringBuilder.Append(string.Format("{0} {1},", dt.Columns[i].ColumnName, strType));
- else
- stringBuilder.Append(string.Format("{0} {1})", dt.Columns[i].ColumnName, strType));
- }
- using (OleDbConnection objConn = new OleDbConnection(connString))
- {
- OleDbCommand objCmd = new OleDbCommand();
- objCmd.Connection = objConn;
- //插入新表
- objCmd.CommandText = stringBuilder.ToString();
- try
- {
- objConn.Open();
- //插入新表
- objCmd.ExecuteNonQuery();
- }
- catch (Exception e)
- {
- return "在Excel中创建表失败!错误信息:" + e.Message;
- }
- stringBuilder.Remove(0, stringBuilder.Length);
- stringBuilder.Append("INSERT INTO ");
- stringBuilder.Append(dt.TableName + " ( ");
- //先插入标头
- for (int i = 0; i < iCols; i++)
- {
- if (i < iCols - 1)
- stringBuilder.Append(dt.Columns[i].ColumnName + ",");
- else
- stringBuilder.Append(dt.Columns[i].ColumnName + ") values (");
- }
- for (int i = 0; i < iCols; i++)
- {
- if (i < iCols - 1)
- stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");
- else
- stringBuilder.Append("@" + dt.Columns[i].ColumnName + ")");
- }
- //建立插入动作的Command
- objCmd.CommandText = stringBuilder.ToString();
- OleDbParameterCollection oleParam = objCmd.Parameters;
- oleParam.Clear();
- for (int i = 0; i < iCols; i++)
- {
- OleDbType oleDbType = ExcelOperation.GetRefOleDataType(dt.Columns[i]);
- //此处是本版本改进中最实用的地方
- oleParam.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, oleDbType));
- }
- //遍历DataTable将数据插入新建的Excel文件中
- foreach (DataRow row in dt.Rows)
- {
- for (int i = 0; i < oleParam.Count; i++)
- {
- oleParam[i].Value = row[i];
- }
- objCmd.ExecuteNonQuery();
- }
- return "数据已成功导入Excel!";
- }
- }
- /// <summary>
- /// 获取与本地DataSet中指定列的类型对应的OleDbType的数据类型字符串
- /// </summary>
- /// <param name="dataColumn"></param>
- /// <returns></returns>
- public static string GetOleDataType(DataColumn dataColumn)
- {
- switch (dataColumn.DataType.Name)
- {
- case "String"://字符串
- {
- return "VarChar";
- }
- case "Double"://数字
- {
- return "Double";
- }
- case "Decimal"://数字
- {
- return "Decimal";
- }
- case "DateTime"://时间
- {
- return "Date";
- }
- default://
- {
- return "VarChar";
- }
- }
- }
- /// <summary>
- /// 获取与本地DataSet中指定列的类型对应的OleDbType类型
- /// </summary>
- /// <param name="dataColumn"></param>
- /// <returns></returns>
- public static OleDbType GetRefOleDataType(DataColumn dataColumn)
- {
- switch (dataColumn.DataType.Name)
- {
- case "String"://字符串
- {
- return OleDbType.VarChar;
- }
- case "Double"://数字
- {
- return OleDbType.Double;
- }
- case "Decimal"://数字
- {
- return OleDbType.Decimal;
- }
- case "DateTime"://时间
- {
- return OleDbType.Date;
- }
- default:
- {
- return OleDbType.VarChar;
- }
- }
- }
- public static string GetExcelConnection(string strFilePath)
- {
- if (!File.Exists(strFilePath))
- {
- throw new Exception("指定的Excel文件不存在!");
- }
- return
- @"Provider=Microsoft.Jet.OLEDB.4.0;" +
- @"Data Source=" + strFilePath + ";" +
- @"Extended Properties=" + Convert.ToChar(34).ToString() +
- @"Excel 8.0;" + "Imex=2;HDR=Yes;" + Convert.ToChar(34).ToString();
- }
2.不利用office组件,而是用TextWriter的某些子类编写器将字节流写入文件。
这个提供一个类库的使用吧,就不发代码了4百多行,可以到下面的网址去下。项目是vs2003做的要转换下然后把RKLib.ExportData.dll复制到bin下引用(或直接引用)然后就可以直接用了
导出到excel的代码: RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");
objExport.ExportDetails(dt, Export.ExportFormat.Excel, saveFileDialog1.FileName);
这个还提供了web导出和CSV格式导出。
网址:http://www.codeproject.com/KB/aspnet/ExportClassLibrary.aspx
利用office组件。这类代码网上很多。
代码1:
- ///
- //Purpose:Excel文件导入导出,需引用Microsoft Excel 11.0 Object Library (需安装excel)
- //Author: Dangmy
- //Date: 2007-03-09
- //Version: 1.0
- ///
- public class ExcelIO
- {
- private int _ReturnStatus;
- private string _ReturnMessage;
- /// <summary>
- /// 执行返回状态
- /// </summary>
- public int ReturnStatus
- {
- get{return _ReturnStatus;}
- }
- /// <summary>
- /// 执行返回信息
- /// </summary>
- public string ReturnMessage
- {
- get{return _ReturnMessage;}
- }
- public ExcelIO()
- {
- }
- /// <summary>
- /// 导入EXCEL到DataSet
- /// </summary>
- /// <param name="fileName">Excel全路径文件名</param>
- /// <returns>导入成功的DataSet</returns>
- public DataSet ImportExcel(string fileName)
- {
- //判断是否安装EXCEL
- Excel.Application xlApp=new Excel.ApplicationClass();
- if(xlApp==null)
- {
- _ReturnStatus = -1;
- _ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
- return null;
- }
- //判断文件是否被其他进程使用
- Excel.Workbook workbook;
- try
- {
- workbook = xlApp.Workbooks.Open(fileName,0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
- }
- catch
- {
- _ReturnStatus = -1;
- _ReturnMessage = "Excel文件处于打开状态,请保存关闭";
- return null;
- }
- //获得所有Sheet名称
- int n = workbook.Worksheets.Count;
- string[] SheetSet = new string[n];
- System.Collections.ArrayList al = new System.Collections.ArrayList();
- for(int i=1; i<=n; i++)
- {
- SheetSet[i-1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
- }
- //释放Excel相关对象
- workbook.Close(null,null,null);
- xlApp.Quit();
- if(workbook != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
- workbook = null;
- }
- if(xlApp != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
- xlApp = null;
- }
- GC.Collect();
- //把EXCEL导入到DataSet
- DataSet ds = new DataSet();
- string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+ fileName +";Extended Properties=Excel 8.0" ;
- using(OleDbConnection conn = new OleDbConnection (connStr))
- {
- conn.Open();
- OleDbDataAdapter da;
- for(int i=1; i<=n; i++)
- {
- string sql = "select * from ["+ SheetSet[i-1] +"$] ";
- da = new OleDbDataAdapter(sql,conn);
- da.Fill(ds,SheetSet[i-1]);
- da.Dispose();
- }
- conn.Close();
- conn.Dispose();
- }
- return ds;
- }
- /// <summary>
- /// 把DataTable导出到EXCEL
- /// </summary>
- /// <param name="reportName">报表名称</param>
- /// <param name="dt">数据源表</param>
- /// <param name="saveFileName">Excel全路径文件名</param>
- /// <returns>导出是否成功</returns>
- public bool ExportExcel(string reportName,DataTable dt,string saveFileName)
- {
- if(dt==null)
- {
- _ReturnStatus = -1;
- _ReturnMessage = "数据集为空!";
- return false;
- }
- bool fileSaved=false;
- Excel.Application xlApp=new Excel.ApplicationClass();
- if(xlApp==null)
- {
- _ReturnStatus = -1;
- _ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
- return false;
- }
- Excel.Workbooks workbooks=xlApp.Workbooks;
- Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
- Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
- worksheet.Cells.Font.Size = 10;
- Excel.Range range;
- long totalCount=dt.Rows.Count;
- long rowRead=0;
- float percent=0;
- worksheet.Cells[1,1]=reportName;
- ((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12;
- ((Excel.Range)worksheet.Cells[1,1]).Font.Bold = true;
- //写入字段
- for(int i=0;i<dt.Columns.Count;i++)
- {
- worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName;
- range=(Excel.Range)worksheet.Cells[2,i+1];
- range.Interior.ColorIndex = 15;
- range.Font.Bold = true;
- }
- //写入数值
- for(int r=0;r<dt.Rows.Count;r++)
- {
- for(int i=0;i<dt.Columns.Count;i++)
- {
- worksheet.Cells[r+3,i+1]=dt.Rows[r][i].ToString();
- }
- rowRead++;
- percent=((float)(100*rowRead))/totalCount;
- }
- range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
- range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
- if( dt.Rows.Count > 0)
- {
- range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
- range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
- range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
- }
- if(dt.Columns.Count>1)
- {
- range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
- range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
- range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
- }
- //保存文件
- if(saveFileName!="")
- {
- try
- {
- workbook.Saved =true;
- workbook.SaveCopyAs(saveFileName);
- fileSaved=true;
- }
- catch(Exception ex)
- {
- fileSaved=false;
- _ReturnStatus = -1;
- _ReturnMessage = "导出文件时出错,文件可能正被打开!/n"+ex.Message;
- }
- }
- else
- {
- fileSaved=false;
- }
- //释放Excel对应的对象
- if(range != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
- range = null;
- }
- if(worksheet != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
- worksheet = null;
- }
- if(workbook != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
- workbook = null;
- }
- if(workbooks != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
- workbooks = null;
- }
- xlApp.Application.Workbooks.Close();
- xlApp.Quit();
- if(xlApp != null)
- {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
- xlApp = null;
- }
- GC.Collect();
- return fileSaved;
- }
- }
参考:
3.http://dr.net.blog.163.com/blog/static/226451372007621082489/