C# 操作Excel

最新推荐文章于 2021-05-18 11:45:10 发布
最新推荐文章于 2021-05-18 11:45:10 发布
阅读量544 收藏
点赞数
分类专栏： ASP.NET 文章标签： c# excel string dataset null object
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ceozhg2009/article/details/7225075
版权 


using System;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using OfficeExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Collections;

namespace BenQGuru.eSCM.WebOrder.ExcelLogic
{
    public class ExcelOperation
    {
        /// <summary>
        /// 将Excel数据导入到DataSet中
        /// </summary>
        /// <param name = "excelPath">Excel路径</param>
        /// <returns></returns>        
        public  DataSet GetExcelData(string excelPath)
        {
            OleDbConnection oleConn = null;
            DataSet excelDataSet = new DataSet();
            try
            {
                string connString = "Provider = Microsoft.Jet.OleDb.4.0;" + "data source = "
                    + excelPath.Trim() + ";Extended Properties = 'Excel 8.0;HDR = Yes;IMEX = 1'";
                oleConn = new OleDbConnection(connString);
                oleConn.Open();
                DataTable schemaTable = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim().ToUpper();

                string sqlCondition = "SELECT * FROM [" + tableName + "] ";
                OleDbCommand oleCmd = new OleDbCommand(sqlCondition, oleConn);
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
                oleAdapter.SelectCommand = oleCmd;
                oleAdapter.Fill(excelDataSet);
            }
            catch
            {
                throw;
            }
            finally
            {
                oleConn.Close();
            }
            return excelDataSet;
        }


       /// <summary>
        /// 根据的DataTable生成Excel
       /// </summary>
        /// <param name="templateFilePath">模版文件路径</param>
       /// <param name="outputSheetIndex">输出到第几个Sheet</param>
        /// <param name="outputFilePath">生成Excel文件路径</param>
        /// <param name="targetSource">数据源DataTable</param>
        public  void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath, DataTable targetSource)
        {
            File.Copy(templateFilePath, outputFilePath);

            #region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
            OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
            object missing = Missing.Value;

            //选择Excel的作用域
            excelApp.Visible = false;
            excelApp.DisplayAlerts = false;
            excelApp.AskToUpdateLinks = false;
            excelApp.AlertBeforeOverwriting = false;

            excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, missing, missing);
            OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
            try
            {
                OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
                for (int i = 0; i < targetSource.Rows.Count; i++)
                {
                    for (int j = 0; j < targetSource.Columns.Count; j++)
                    {
                        xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
                    }
                }
                workBook.Save();
            }
            catch
            {
                throw;
            }
            finally
            {
                //清除资源，释放内存
                workBook.Close(missing, missing, missing);
                excelApp.Workbooks.Close();
                excelApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                GC.Collect();
            }
            #endregion
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="templateFilePath"></param>
		/// <param name="outputSheetIndex"></param>
		/// <param name="outputFilePath"></param>
		/// <param name="hsHead">key为excel中写入的位置，value为在位置写入的值</param>
		/// <param name="iDetailStart"></param>
		/// <param name="dtDetail"></param>
		public  void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath,Hashtable hsHead,int iDetailStart,DataTable dtDetail,string exportColumnNames)
		{
			File.Copy(templateFilePath, outputFilePath);

			#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
			OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
			object missing = Missing.Value;

			//选择Excel的作用域
			excelApp.Visible = false;
			excelApp.DisplayAlerts = false;
			excelApp.AskToUpdateLinks = false;
			excelApp.AlertBeforeOverwriting = false;

			excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
				missing, missing, missing, missing, missing, missing, missing, missing, missing);
			OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
			try
			{
				OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
				//写入主档
				string _strKey = "";
				string _strValue = "";
				foreach(DictionaryEntry de in hsHead)
				{
					_strKey = de.Key.ToString();					
					_strValue = de.Value.ToString();
					string[] _cellIndexs = _strKey.Split(',');	
					xItems.Cells[int.Parse(_cellIndexs[0]),int.Parse(_cellIndexs[1])] = _strValue;
				}

				string[] _arrColumnNames = exportColumnNames.Split(',');	

				//写入明细
				for (int i = 0; i < dtDetail.Rows.Count; i++)
				{
					for (int j = 0; j < _arrColumnNames.Length; j++)
					{
						xItems.Cells[iDetailStart + i + 2, j + 1] = dtDetail.Rows[i][_arrColumnNames[j].ToString()].ToString();
					}
				}
				workBook.Save();
			}
			catch
			{
				throw;
			}
			finally
			{
				//清除资源，释放内存
				workBook.Close(missing, missing, missing);
				excelApp.Workbooks.Close();
				excelApp.Quit();

				System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

				GC.Collect();
			}
			#endregion
		}

		/// <summary>
		/// 根据的DataTable生成Excel
		/// </summary>
		/// <param name="templateFilePath">模版文件路径</param>
		/// <param name="outputSheetIndex">输出到第几个Sheet</param>
		/// <param name="outputFilePath">生成Excel文件路径</param>
		/// <param name="targetSource">数据源DataTable</param>
		/// /// <param name="targetHead">excel表头的数据源Hashtable</param>
		public  void ExportDataTableToExcel(string templateFilePath,int outputSheetIndex, string outputFilePath, DataTable targetSource,Hashtable targetHead)
		{
			File.Copy(templateFilePath, outputFilePath);

			#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
			OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
			object missing = Missing.Value;

			//选择Excel的作用域
			excelApp.Visible = false;
			excelApp.DisplayAlerts = false;
			excelApp.AskToUpdateLinks = false;
			excelApp.AlertBeforeOverwriting = false;

			excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
				missing, missing, missing, missing, missing, missing, missing, missing, missing);
			OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
			try
			{
				OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
				for (int i = 0; i < targetHead.Count; i++)//保存表头
				{
						xItems.Cells[1, i + 1] = targetHead[i].ToString();
				}
				for (int i = 0; i < targetSource.Rows.Count; i++)
				{
					for (int j = 0; j < targetSource.Columns.Count; j++)
					{
						xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
					}
				}
				workBook.Save();
			}
			catch
			{
				throw;
			}
			finally
			{
				//清除资源，释放内存
				workBook.Close(missing, missing, missing);
				excelApp.Workbooks.Close();
				excelApp.Quit();

				System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

				GC.Collect();
			}
			#endregion
		}
        //add by jodie 
        /// <summary>
        /// 根据的DataTable生成Excel （for Maintain_Liablity页面） 
        /// </summary>
        /// <param name="templateFilePath">模版文件路径</param>
        /// <param name="outputSheetIndex">输出到第几个Sheet</param>
        /// <param name="outputFilePath">生成Excel文件路径</param>
        /// <param name="targetSource">数据源DataTable</param>
        public  void ExportDataTableToExcelForMaintainLiability(string templateFilePath, int outputSheetIndex, string outputFilePath, DataTable targetSource)
        {
            File.Copy(templateFilePath, outputFilePath);

            #region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
            OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
            object missing = Missing.Value;

            //选择Excel的作用域
            excelApp.Visible = false;
            excelApp.DisplayAlerts = false;
            excelApp.AskToUpdateLinks = false;
            excelApp.AlertBeforeOverwriting = false;

            excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, missing, missing);
            OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
            try
            {
                int iColor = System.Drawing.Color.Yellow.ToArgb(); //颜色转换，转成int
                OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
                for (int i = 0; i < targetSource.Rows.Count; i++)
                {
                    for (int j = 0; j < targetSource.Columns.Count; j++)
                    {
                        if(j < targetSource.Columns.Count-1)
                        {
                            xItems.Cells[i + 2, j + 1] = targetSource.Rows[i][j].ToString();
                            if (targetSource.Rows[i]["FLAG"].ToString().Trim() == "1")
                            {
                                xItems.get_Range(xItems.Cells[i + 2, j + 1], xItems.Cells[i + 2, j + 1]).Interior.Color = iColor;
                                //xItems.get_Range(xItems.Cells[i + 2, j + 1], xItems.Cells[i + 2, j + 1]).Interior.Color = 11;
                            }
                        }
                    }
                }
                workBook.Save();
            }
            catch(Exception ex)
            {
                throw;
            }
            finally
            {
                //清除资源，释放内存
                workBook.Close(missing, missing, missing);
                excelApp.Workbooks.Close();
                excelApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

                GC.Collect();
            }
            #endregion
        }
        //end add by jodie
		#region 导出账款查询的数据
		/// <summary>
		/// 根据的DataTable生成Excel
		/// 重写by fly xia 2010-11-24
		/// </summary>
		/// <param name="templateFilePath">模版文件路径</param>
		/// <param name="outputSheetIndex">输出到第几个Sheet</param>
		/// <param name="outputFilePath">生成Excel文件路径</param>
		/// <param name="targetSource">数据源DataTable</param>
		public void ExportDataTableToExcelWithStyle(string templateFilePath, int outputSheetIndex, string outputFilePath, DataTable targetSource, System.Collections.Hashtable htStyle)
		{
			File.Copy(templateFilePath, outputFilePath);

			#region 调用Excel Com来将生成的XmlExcel转化为二进制的Excel
			OfficeExcel.ApplicationClass excelApp = new OfficeExcel.ApplicationClass();
			object missing = Missing.Value;

			//选择Excel的作用域
			excelApp.Visible = false;
			excelApp.DisplayAlerts = false;
			excelApp.AskToUpdateLinks = false;
			excelApp.AlertBeforeOverwriting = false;

			excelApp.Application.Workbooks.Open(outputFilePath, missing, missing, missing, missing, missing,
				missing, missing, missing, missing, missing, missing, missing, missing, missing);
			OfficeExcel.Workbook workBook = excelApp.Workbooks[outputSheetIndex];
			try
			{
				//获取样式数据
				int _intTitleRow = 1;
				int _intGridTitleRow = 5;
				int _intBgColor = 14;
				int _intFontColor = 53;
				string _strTitle = "阿里斯顿热能产品（中国）有限公司对账单";

				if (htStyle.Count > 0)
				{
					if (htStyle["titlerow"] != null)
					{
						_intTitleRow = int.Parse(htStyle["titlerow"].ToString());
						if (htStyle["row"] != null)
							_intGridTitleRow = int.Parse(htStyle["row"].ToString());
						//if (htStyle["bgcolor"] != null)
						//    _intBgColor = int.Parse(htStyle["bgcolor"].ToString());
						if (htStyle["fontcolor"] != null)
							_intFontColor = int.Parse(htStyle["fontcolor"].ToString());
					}
					if (htStyle["title"] != null)
					{
						_strTitle = htStyle["title"].ToString();
					}
				}

				//向Excel中写入数据
				OfficeExcel.Worksheet xItems = (OfficeExcel.Worksheet)workBook.Sheets[1];
				for (int i = 0; i < targetSource.Rows.Count; i++)
				{
					for (int j = 0; j < targetSource.Columns.Count; j++)
					{
						xItems.Cells[i + _intTitleRow + 1, j + 1] = targetSource.Rows[i][j].ToString();
					}
				}
				xItems.Cells[1, 1] = _strTitle;//标题

				//设置样式
				if (htStyle["titlerow"] != null && htStyle["row"] != null)
				{
					设置Grid标题行的样式
					//for (int j = 0; j < targetSource.Columns.Count; j++)
					//{
					//    if (htStyle["bgcolor"] != null)
					//        ((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 1, j + 1]).Interior.ColorIndex = _intBgColor;//背景颜色
					//    ((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 1, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
					//}

					//设置Grid内容的的特殊样式
					if (htStyle["fontcolor"] != null && _intGridTitleRow < targetSource.Rows.Count-1)
					{
						//设置Grid标题行下一行的样式
						((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 2, 4]).Font.ColorIndex = _intFontColor;
						((Microsoft.Office.Interop.Excel.Range)xItems.Cells[_intTitleRow + _intGridTitleRow + 2, 7]).Font.ColorIndex = _intFontColor;
						//设置Grid最后一行的样式（(targetSource.Rows.Count-1)+_intTitleRow+1）
						((Microsoft.Office.Interop.Excel.Range)xItems.Cells[targetSource.Rows.Count + _intTitleRow, 4]).Font.ColorIndex = _intFontColor;
						((Microsoft.Office.Interop.Excel.Range)xItems.Cells[targetSource.Rows.Count + _intTitleRow, 7]).Font.ColorIndex = _intFontColor;
					}

					//设置Grid内容的的一般样式
					for (int i = _intGridTitleRow; i < targetSource.Rows.Count; i++)
					{
						for (int j = 0; j < targetSource.Columns.Count; j++)
						{
							((Microsoft.Office.Interop.Excel.Range)xItems.Cells[i + _intTitleRow + 2, j + 1]).Font.Size = 10;//字体大小
							if(j<4)
								((Microsoft.Office.Interop.Excel.Range)xItems.Cells[i + _intTitleRow + 2, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;//靠左
						}
					}
				}

				workBook.Save();
			}
			catch
			{
				throw;
			}
			finally
			{
				//清除资源，释放内存
				workBook.Close(missing, missing, missing);
				excelApp.Workbooks.Close();
				excelApp.Quit();

				System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

				GC.Collect();
			}
			#endregion
		}

		#endregion
    }

    

}



ceozhg2009
关注
专栏目录
Excel数据的导入
m0_57727153的博客
07-09 636
一、实现功能 将创建好的Excel文件内容导入到渲染好的表格中； 初始状态如下： 图一：点击导入的初始状态 功能介绍：如图点击导入按钮弹出导入学生的模态窗体内容如图一，1.将想要的模板提前准备好点击下载按钮经过控制器代码执行判断返回文件如图二 如图二：判断模板导入 思路：1、获取读取的文件； 4、利用NPOI把内存流中的数据读取成Excel 将模板成功导入后，点击选择文件弹出文件夹，选择你的模板 图三：模板的...
从excel表格导入数据
weixin_44549995的博客
05-08 214
思路： （1）下载需要导入文件的模板 （2）判断导入文件类型是不是后缀: .xls 1、获取读取的文件； 2、把文件转换为二进制数组；3、二进制数组转成内存流；4、利用NPOI把内存流中的数据读取成工作簿Excel （3）工作簿中有工作表 （4）工作表有数据 （5）数据准确性（学院 专业 年级 班级 表跟表之间的关系） （6）数据都有,然后读取数据。 （1）下载需要导入文件的模板 要想将...
Unity/C#的Excel转二进制和XML工具
01-29
工具是在UNITY编程环境下使用的，若个人需要可视情况改写，与EXCEL的交互是使用EPPlus插件，插件附带在资源里面，可满足大部分的转换需求
C#操作Excel
03-17
综上所述，C#操作Excel涉及了文件读写、数据绑定、异常处理等多个方面，而与DataGridView的结合使用，使数据的可视化和交互变得更加便捷。无论是企业级应用还是个人项目，这些技术都是处理Excel数据的强大工具。在...
C#操作Excel的几种方法
07-03
### C#操作Excel的几种方法 #### 概述 在.NET框架中，通过C#语言进行Excel文件的操作是一项常见的需求，比如数据导入导出、报表生成等场景。本文将详细介绍几种利用C#来操作Excel的方法，并重点分析一种基于`...
C#操作Excel.rar_C#操作 excel_C#操作excel_c# conExcel_excel_gift7x8
07-15
本资源"**C#操作Excel.rar**"提供了一种利用C#语言来操作Excel的方法，其功能强大，能够满足多种需求。 C#操作Excel主要依赖于Microsoft Office Interop库或第三方库，如EPPlus、NPOI等。Interop库允许C#代码直接与...
C#操作excel
05-24
总结，C#操作Excel主要涉及对Excel文件的读写、数据的导入导出、样式设置以及公式计算等。根据项目需求，可以选择使用Microsoft.Office.Interop.Excel库或者第三方库如EPPlus。理解这些知识点将有助于在实际开发中更...
C# 将二进制数据转换成BASE64字符串的方法
Diamond、的博客
11-09 7380
//编码例子 System.IO.FileStream fs = System.IO.File.OpenRead("c://1.jpg"); byte[] dt = new byte[fs.Length]; fs.Read(dt, 0, (int)fs.Length); fs.Close(); string s = Convert.ToBase64String(dt); f
C# Microsoft.Office 操作Excel总结
05-18 1654
1、导入命名空间： using Microsoft.Office.Interop.Excel; 2、如何打开已有excel文档，或者创建一个新的excel文档 Application app = new Application(); Workbooks wbks = app.Workbooks; _Workbook _wbk = wbks.Add(excelTempPath + "quotaExcelTemp.xls"); Sheets shs = _wbk.Sheets; _Wor..
学习（一）C#利用窗体打开Excel文件进行正常访问和写入
weixin_39699494的博客
08-07 4260
首先，在对Excel文件进行操作时，首先应该注意引用（word为一起加的，这里为非必要） using MSWord = Microsoft.Office.Interop.Word; using MSExcel = Microsoft.Office.Interop.Excel; （1）、能打开指定的Excel文件 private void button1_Click(obj
C#导出Excel文件
starhuhu的专栏
12-07 447
private void buttonX2_Click(object sender, EventArgs e)         {             FileDialog saveFD = new SaveFileDialog();             saveFD.InitialDirectory = Environment.GetFolderPath(Environment.S
C#对EXCEL文件操作
jjning12345的专栏
03-19 945
using Microsoft.Office.Interop.Excel;  using System.Reflection;  public class MyConsole  {      public static void Main()      {          Application app = new ApplicationClass();          try
C# 关于转换成二进制位数不够时补齐位数PadLeft（）
qq_21489689的博客
11-17 7801
//如十进制数字1，转化为8位二进制 int a=1; string b=Convert.ToString(1,2); //但是得到的字符串是"1"而不是"00000001" //这就需要补齐位数了 string b=Convert.ToString(1,2).PadLeft(8,'0');//PadLeft(int a,cha b),其中a为总共多少位，b为用什么补齐
转帖：C#操作Excel
布衣者的专栏
09-21 1226
一个自己测试通过的操作EXCEL using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using
c#中文件与二进制流文件的转换
08-07 9051
c# SQL数据库image图片文件的保存于读取            SqlConnection con = new SqlConnection("Persist Security Info=False;User id=sa;pwd=123;database=WDERPDATA_001;server=(local)");            con.Open();            S
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值