白开心
  IT博客 :: 首页 ::  :: 联系 :: 聚合  :: 管理 ::
  9 随笔 :: 76 文章 :: 28 评论 :: 0 Trackbacks
Excel文档

 

  1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using Excel;
  5using System.Reflection;
  6
  7namespace CW
  8{
  9    public class myExcel
 10    {
 11        private ApplicationClass excelApp;
 12
 13        private Workbook excelBook;
 14
 15        Object Nothing = System.Reflection.Missing.Value;
 16
 17
 18        /// <summary>
 19        /// 修改 Excel 的属性
 20        /// </summary>
 21        /// <param name="ExcelPath"></param>
 22        public void ModifyMemo(string ExcelPath)
 23        {
 24            excelApp = new ApplicationClass();
 25            excelApp.Visible = false;
 26            excelApp.DisplayAlerts = false;
 27            excelApp.AlertBeforeOverwriting = false;
 28            excelApp.AskToUpdateLinks = false;
 29            //
 30            excelBook = excelApp.Workbooks.Open(ExcelPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
 31
 32            object props = excelBook.BuiltinDocumentProperties;
 33            object _p = props.GetType().InvokeMember("", BindingFlags.GetProperty, null, props, new object[] { "Subject" });
 34            _p.GetType().InvokeMember("", BindingFlags.SetProperty, null, _p, new object[] { "修改的值" });
 35
 36            excelBook.Save();
 37
 38            if (excelBook != null)
 39            {
 40                excelBook.Close(Nothing, Nothing, Nothing);
 41                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
 42                excelBook = null;
 43
 44                excelApp.Application.Quit();
 45                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
 46                excelApp = null;
 47            }
 48            GC.Collect();
 49            GC.WaitForPendingFinalizers();
 50        }
 51
 52                /// <summary>
 53        /// 列号到列ID转换
 54        /// </summary>
 55        /// <param name="columnNum">列号数字</param>
 56        /// <returns>列ID，如 A、AB等，1返回A</returns>
 57        public static string ColumnNumToColumnID(int columnNum)
 58        {
 59            if (columnNum < 1)
 60            {
 61                return "";
 62            }
 63            int res, left;
 64            string ID = String.Empty;
 65            res = (columnNum - 1) / 26;
 66            left = columnNum - res * 26;
 67            ID = left.ToString();
 68
 69            columnNum = res;
 70            while (columnNum > 26)
 71            {
 72                res = (columnNum - 1) / 26;
 73                left = columnNum - res * 26;
 74                ID = left.ToString() + "|" + ID;
 75                columnNum = res;
 76            }
 77            if (columnNum != 0)
 78            {
 79                ID = columnNum.ToString() + "|" + ID;
 80            }
 81
 82            String[] str = ID.Split('|');
 83            ID = String.Empty;
 84            for (int i = 0; i < str.Length; i++)
 85            {
 86                ID += Convert.ToString((char)((int)('A') + Convert.ToInt16(str[i]) - 1));
 87            }
 88            return ID;
 89        }
 90
 91        /// <summary>
 92        /// 列 ID 到  列号的转换 
 93        /// </summary>
 94        /// <param name="cloumnID"></param>
 95        /// <returns>A返回1</returns>
 96        public static int ColumnIDToColumnNum(string columnID)
 97        {
 98            int res = 0;
 99            for (int i = 0; i < columnID.Length; i++)
100            {
101                res += ((int)columnID[i] - (int)('A') + 1) * (int)Math.Pow(26, (columnID.Length - i - 1));
102            }
103            return res;
104        }
105
106        /// <summary>
107        /// 获取单元格的值
108        /// </summary>
109        /// <param name="row"></param>
110        /// <param name="col"></param>
111        /// <param name="worksheet"></param>
112        /// <returns></returns>
113        public string getExcelCellValue(int row, int col, Excel.Worksheet worksheet)
114        {
115            string strCellID = string.Format("{0}{1}", ColumnNumToColumnID(col), row);
116
117            Excel.Range Cell = worksheet.get_Range(strCellID, Type.Missing);
118            object objContent = (Cell.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty, null, Cell, null));
119            if (objContent == null || objContent.ToString().Trim() == String.Empty)
120            { ///该单元格为空值，被允许
121                return string.Empty;
122            }
123            else
124            {
125                ///如果能转换为浮点型数据
126                return objContent.ToString();
127            }
128        }
129
130
131        /// <summary>
132        /// 清除颜色定义
133        /// </summary>
134        /// <param name="area"></param>
135        private void clearRuleColor(ICellArea area)
136        {
137            string startCell, endCell;
138            startCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(area.StartCol), area.StartRow);
139            endCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(area.EndCol), area.EndRow);
140            Excel.Range range = ExcelWorksheet.get_Range(startCell, endCell);
141
142            if (range != null)
143            {
144                range.Interior.ColorIndex = 0;
145            }
146        }
147
148        /// <summary>
149        /// 设置区域颜色
150        /// </summary>
151        /// <param name="area"></param>
152        /// <param name="color"></param>
153        private void setRuleColor(int startRow,int startCol,int endRow,int endCol, int color)
154        {
155            区域设置背景颜色方法 ----
170
171            /*  颜色定义方法
172            int Yellow = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 255, 0));
173            int Red = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
174            int Blue = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(0, 0, 255));
175            int White = System.Drawing.ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
176            */
177        }
178
179        /// <summary>
180        /// 获取选中区域
181        /// </summary>
182        private void getSelectRange()
183        {
184            /*
185            Excel.Range range = worksheet.Application.Selection as Excel.Range;
186            StartRow = range.Row;
187            StartCol = range.Column;
188            EndRow = range.Row + range.Rows.Count - 1;
189            EndCol = range.Column + range.Columns.Count - 1;
190            */
191        }
192
193
194        /// <summary>
195        /// 返回Excel文档所有的Sheet
196        /// </summary>
197        /// <param name="excelPath"></param>
198        /// <returns></returns>
199        public List<string> getSheetFromExcel(string excelPath)
200        {
201            List<string> Sheets = new List<string>();
202            OleDbConnection Conn = new OleDbConnection();
203            Conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + excelPath + ";Extended Properties=Excel 8.0;Persist Security Info=False";
204            Conn.Open();
205            System.Data.DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
206
207            int count = dt.Rows.Count;
208            if (count == 0)
209                return null;
210
211            for (int i = 0; i < count; i++)
212            {
213                Sheets.Add(dt.Rows[i]["TABLE_NAME"].ToString());
214            }
215            return Sheets;
216        }
217
218        /// <summary>
219        /// 把Excel文档转换为Xml文档
220        /// </summary>
221        /// <param name="excelPath"></param>
222        /// <param name="xmlPath"></param>
223        public void changeExcelToXml(string excelPath, string xmlPath)
224        {
225            Object Nothing = System.Reflection.Missing.Value;
226            ApplicationClass excelApp;
227            Workbook excelBook;
228            excelApp = new ApplicationClass();
229            excelApp.Visible = false;
230            excelApp.DisplayAlerts = false;
231            excelApp.AlertBeforeOverwriting = false;
232            excelApp.AskToUpdateLinks = false;
233            //
234            excelBook = excelApp.Workbooks.Open(excelPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
235            excelBook.SaveAs(xmlPath, XlFileFormat.xlXMLSpreadsheet, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlNoChange, Nothing, Nothing, Nothing, Nothing, Nothing);
236            //close
237            excelBook.Close(Nothing, Nothing, Nothing);
238            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
239            excelBook = null;
240
241            excelApp.Application.Quit();
242            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
243            excelApp = null;
244
245            GC.Collect();
246            GC.WaitForPendingFinalizers();
247        }
248
249
250        /// <summary>
251        /// 把 Xml 格式的 Excel 文件转换成 二进制格式的 Excel 文件
252        /// </summary>
253        /// <param name="xlsPath"></param>
254        public void RepairXls(string xlsPath)
255        {
256            Workbook excelBook = null;
257            ApplicationClass excelApp = null;
258            object Nothing = System.Reflection.Missing.Value;
259            excelApp = new ApplicationClass();
260            excelApp.Visible = false;
261            excelApp.DisplayAlerts = false;
262            excelApp.AlertBeforeOverwriting = false;
263            excelApp.AskToUpdateLinks = false;
264
265            excelBook = excelApp.Workbooks.Open(xlsPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
266            excelBook.SaveAs(xlsPath, XlFileFormat.xlExcel5, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlNoChange, Nothing, Nothing, Nothing, Nothing, Nothing);
267
268            if (excelBook != null)
269            {
270                excelBook.Close(Nothing, Nothing, Nothing);
271                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
272                excelBook = null;
273
274                excelApp.Application.Quit();
275                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
276                excelApp = null;
277            }
278            GC.Collect();
279            GC.WaitForPendingFinalizers();
280        }
281    }
282}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Excel;
using System.Reflection;
using System.Windows.Forms;

namespace Gold
{
    public class ExcelHandler
    {
        private ExcelHandler() { }

        private static ExcelHandler _instance = null;
        public static ExcelHandler Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new ExcelHandler();
                return _instance;
            }
        }

        private object MissingValue
        {
            get { return Missing.Value; }
        }

        private Excel.ApplicationClass excelApp = null;
        private Excel.Workbook excelBook = null;
        private Excel.Worksheet excelSheet = null;

        /// <summary>
        /// 创建 Excel 对象
        /// </summary>
        /// <returns></returns>
        public ApplicationClass CreateApplication()
        {
            excelApp = new Excel.ApplicationClass();
            excelApp.Visible = false;
            excelApp.DisplayAlerts = false;
            excelApp.AlertBeforeOverwriting = false;
            excelApp.AskToUpdateLinks = false;
            return excelApp;
        }

        /// <summary>
        /// 消亡 Excel 对象和进程
        /// </summary>
        private bool DisposeExcel()
        {
            try
            {
                if (excelBook != null && excelApp != null)
                {
                    excelBook.Close(MissingValue, MissingValue, MissingValue);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
                    excelBook = null;

                    excelApp.Application.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                return true;
            }
            catch { return false;}
        }

        /// <summary>
        /// 保存 Excel 文件
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private bool SaveExcel(string path)
        {
            try 
            {
                excelBook.SaveAs(path, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, Excel.XlSaveAsAccessMode.xlExclusive, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 打开 Excel 文档
        /// </summary>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        private Workbook OpenExcel(String excelPath)
        {
            excelBook = excelApp.Workbooks.Open(excelPath, MissingValue, MissingValue, MissingValue, MissingValue,
                                                        MissingValue, MissingValue, MissingValue, MissingValue,
                                                        MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
            return excelBook;
        }

        /// <summary>
        /// DataGridView 导出 Excel 文档
        /// </summary>
        /// <param name="dgv"></param>
        public static void ExportToExcel(DataGridView dgv)
        {
            Excel.Application myExcel = new Excel.Application();
            myExcel.Application.Workbooks.Add(true);
            myExcel.Visible = true;
            //生成标题
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                myExcel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
                Range range = (Range)myExcel.Cells[1, i + 1];
                range.ColumnWidth = 15;
                range.Font.FontStyle = "bold";
            }
            //填充数据  
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    myExcel.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value + String.Empty;
                }
            }
        }

        /// <summary>
        /// 有模板的导出 Excel 数据
        /// </summary>
        /// <param name="templatePath"></param>
        /// <param name="dtHistory"></param>
        /// <param name="SavePath"></param>
        /// <returns></returns>
        public bool ExportHistoryExcel(String templatePath,System.Data.DataTable dtHistory, String SavePath)
        {
            try
            {
                /// 创建 ExcelApplication 对象
                excelApp = CreateApplication();
                /// 打开模板文件
                excelBook = OpenExcel(templatePath);
                /// 获取当前 Worksheet
                excelSheet = (Worksheet)excelBook.ActiveSheet;

                int rowIndex = 2;
                //  写入数据
                foreach (DataRow row in dtHistory.Rows)
                {
                    excelSheet.Cells[rowIndex, 1] = row["CurDate"] +String.Empty;
                    excelSheet.Cells[rowIndex, 2] = row["YCount"] +String.Empty;
                    excelSheet.Cells[rowIndex, 3] = row["YCost"] +String.Empty;
                    rowIndex++;
                }

                return SaveExcel(SavePath);
            }
            catch
            {
                return false;
            }
            finally
            {
                DisposeExcel();
            }
        }
    }
}



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;

namespace TableTOExcel
{
    public class ExcelHandler
    {
        private ApplicationClass excelApp;
        private Workbook excelBook;
        private Worksheet excelSheet;
        private Object Nothing = System.Reflection.Missing.Value;

        public bool ExportToExcel(string templatePath, System.Data.DataTable dtUser)
        {
            try
            {
                excelApp = new ApplicationClass();
                excelApp.Visible = false;
                excelApp.DisplayAlerts = false;
                excelApp.AlertBeforeOverwriting = false;
                excelApp.AskToUpdateLinks = false;

                excelBook = excelApp.Workbooks.Open(templatePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                excelSheet = (Worksheet)excelBook.Worksheets[1];

                foreach (DataRow row in dtUser.Rows)
                {
                    excelSheet.Cells[2, 1] = row["ID"] + string.Empty;
                    excelSheet.Cells[2, 2] = row["UserName"] + string.Empty;
                    excelSheet.Cells[2, 3] = row["Birth"] + string.Empty;
                }
                // 成功时直接打开 Excel
                excelApp.Visible = true;
                return true;
            }
            catch
            {
                // 出错时才结束 Excel 进程
                if (excelBook != null)
                {
                    excelBook.Close(Nothing, Nothing, Nothing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
                    excelBook = null;

                    excelApp.Application.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;

                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
                return false;
            }
            finally
            {

            }

        }

    }
}

posted on 2007-06-08 15:19 白开心 阅读(672) 评论(0)  编辑 收藏 引用 所属分类: .Net（学习ing...）
只有注册用户登录后才能发表评论。