• c# 操作Excel


    基本操作类

     	public class ExcelUtil
        	{
           
            public void ReleaseCOM(object pObj)//释放资源
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
                }
                catch
                {
                    throw new Exception("释放资源时发生错误!");
                }
                finally
                {
                    pObj = null;
                }
            }
            public ExcelUtil(string filePath)
            {
                this.filePath = filePath;
                app = new Microsoft.Office.Interop.Excel.Application();//打开一个Excel应用
                if (app == null)
                {
                    return;
                }
                wbs = app.Workbooks;
                wb = wbs.Add(filePath);
                shs = wb.Sheets;
                int sheetNumber = shs.Count;
                for (int i = 1; i <= sheetNumber; i++)
                {
                    _Worksheet sh = (_Worksheet)shs.get_Item(i);
                    this.ShList.Add(sh);
                }
            }
            /// <summary>
            /// 保存文件
            /// </summary>
            public ExcelUtil(string filePath, int addSheet)
            {
                this.filePath = filePath;
                app = new Microsoft.Office.Interop.Excel.Application();//打开一个Excel应用
                if (app == null)
                {
                    return;
                }
                wbs = app.Workbooks;
                wb = wbs.Add(filePath);
                shs = wb.Sheets;
                int sheetNumber = shs.Count;
                int addSheetCount = addSheet - sheetNumber;
                if (addSheetCount > 0)
                {
                    for (int i = 0; i < addSheetCount; i++)
                    {
                        var sheet = (_Worksheet)shs.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    }
                }
                int currentSheetNumber = shs.Count;
                for (int i = 1; i <= currentSheetNumber; i++)
                {
                    _Worksheet sh = (_Worksheet)shs.get_Item(i);
                    this.ShList.Add(sh);
                }
            }
            /// <summary>
            /// 保存文件
            /// </summary>
            public void save()
            {
                if (filePath != null)
                {
                    FileInfo old = new FileInfo(filePath);
                    if (old.Exists)
                    {
                        File.Delete(filePath);
                    }
                    wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    foreach (_Worksheet inst in shList)
                    {
                        ReleaseCOM(inst);
                    }
                    ReleaseCOM(shs);
                    ReleaseCOM(wb);
                    ReleaseCOM(wbs);
                    app.Quit();
                    ReleaseCOM(app);
                }
            }
            /// <summary>
            /// 杀掉进程的方式保存excel。
            /// 用来解决正常保存时出现的公式异常问题。
            /// </summary>
            public void saveByKillProcess()
            {
                if (filePath != null)
                {
                    try
                    {
                        FileInfo old = new FileInfo(filePath);
                        if (old.Exists)
                        {
                            File.Delete(filePath);
                        }
                        wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        foreach (_Worksheet inst in shList)
                        {
                            ReleaseCOM(inst);
                        }
                        ReleaseCOM(shs);
                        ReleaseCOM(wb);
                        ReleaseCOM(wbs);
                        KillExcel(app);
                    }
                    catch (System.Exception e)
                    {
                        logger.Debug(e.Message);
                    }
                }
            }
    
    
            /// <summary>
            /// 合并Excel单元格
            /// </summary>
            /// <param name="ws">sheet页</param>
            /// <param name="str">要合并单元格的左上角的单元格列号A</param>
            /// <param name="i">要合并的单元格的左上角的单元格行号2</param>
            /// <param name="str3">要合并单元格的右下角的单元格列号B</param>
            /// <param name="i">要合并的单元格的右下角的单元格行号2</param>
            /// <param name="j">表格最后一行的行号</param>
            /// <returns>单元格</returns>
            public static Range CombineExcel(_Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown)
            {
                try
                {
                    string str1 = GetColStrFromInt(colLeft) + rowUp;
                    string str2 = GetColStrFromInt(colRight) + rowDown;
                    Range range = ws.get_Range(str1, str2);
                    range.Merge(0);
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range.Font.Underline = false;
    
                    range.WrapText = true;
                    //  range.EntireColumn.AutoFit();
                    range.Borders.LineStyle = 1;
                    return range;
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                    return null;
                }
            }
    
            /// <summary>
            /// 传入列号得到对应的列名称,从1开始,1代表第A列
            /// </summary>
            /// <param name="col">列号</param>
            /// <returns></returns>
            public static string GetColStrFromInt(int col)
            {
                col = col + 'A' - 1;
                string colStr = "";
                if (col > (int)'Z')
                {
                    colStr = Convert.ToChar((col - 90 - 1) / 26 + 'A').ToString() +
                        Convert.ToChar((col - 90 - 1) % 26 + 'A').ToString();
                    return colStr;
                }
                else
                    return Convert.ToChar(col).ToString();
            }
    
            [DllImport("User32.dll", CharSet = CharSet.Auto)]
            public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
            private void KillExcel(Microsoft.Office.Interop.Excel.Application excel)
            {
                IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄,具体作用是得到这块内存入口
                int k = 0;
                GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
                p.Kill(); //关闭进程k
            }
            private Microsoft.Office.Interop.Excel.Application app;
            private Workbooks wbs;
            private _Workbook wb;
            private Sheets shs;
            private List<_Worksheet> shList = new List<_Worksheet>();
            public List<_Worksheet> ShList
            {
                get
                {
                    return shList;
                }
                set
                {
                    shList = value;
                }
            }
            private string filePath;
            protected readonly static log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    
        }
    

    得到一个合并range插入数据

     		private Range GetCurrentRange(_Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown, string result)
            {
                Range currentRange = null;
                currentRange = ExcelUtil.CombineExcel(ws, colLeft, rowUp, colRight, rowDown);
                currentRange.Value2 = result;插入结果
                currentRange.Borders.LineStyle = 0;//边框线
    			currentRange.Font.ColorIndex = 3;//插入颜色
    			currentRange.Font.Bold = true;//加粗
                currentRange.ColumnWidth = 100;//控制列宽
     			currentRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;//文字的位置
                currentRange.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//下边框加粗
       			var range = ws.Cells;//选中整个文档
                range.Interior.ColorIndex = 2;//填充背景颜色
    			return currentRange;
            }
    

    创建超链接

    		/// <summary>
            /// Inserts the catalogue.目录超链接
            /// </summary>
            /// <param name="ws">The ws.</param>
            /// <param name="colLeft">The col left.</param>
            /// <param name="rowUp">The row up.</param>
            /// <param name="colRight">The col right.</param>
            /// <param name="rowDown">The row down.</param>
            /// <param name="result">The result.</param>
            /// <param name="colWidth">Width of the col.</param>
            /// <param name="sheet">The sheet.</param>
            private void InsertCatalogue(_Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown, string result, string sheet)
            {
                var currentRange = this.GetCurrentRange(ws, colLeft, rowUp, colRight, rowDown, result);
                currentRange.Font.ColorIndex = 5;
                currentRange.Font.Bold = true;
                string hyperlink = "#" + sheet + "!" + ExcelUtil.GetColStrFromInt(colLeft) + rowUp;
                ws.Hyperlinks.Add(currentRange, hyperlink, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                currentRange.Font.Underline = false;
                currentRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
            }
    

    对数据画统计图

      		private void Chart(_Worksheet ws, int startRow, Catalogue catalogue)
            {
                Range chartRange;
    
                ChartObjects xlCharts = (ChartObjects)ws.ChartObjects(Type.Missing);
                ChartObject myChart = (ChartObject)xlCharts.Add(250, 20, 600, 300);
                Chart chartPage = myChart.Chart;
                string title = "标题名字";
                this.InsertResult(ws, 6, 25, 6, 25, title);
                string lowRange = ws.get_Range("K31", Type.Missing).Value2.ToString();
                string hightRange = ws.get_Range("L31", Type.Missing).Value2.ToString();
                double temp = 0;
                bool isAllLine = false;
                string chartRangeSelect = "I31:I" + startRow + ",K31:K" + startRow + ",L31:L" + startRow;
                if (double.TryParse(lowRange, out temp) && double.TryParse(hightRange, out temp))
                {
                    isAllLine = true;
                }
                else
                {
                    if (!double.TryParse(lowRange, out temp))
                    {
                        chartRangeSelect = "I31:I" + startRow + ",L31:L" + startRow;
                    }
                    else
                    {
                        chartRangeSelect = "I31:I" + startRow + ",K31:K" + startRow;
                    }
                }
                chartRange = ws.get_Range(chartRangeSelect, Type.Missing);
                chartPage.ChartWizard(chartRange, XlChartType.xlConeCol, Type.Missing, XlRowCol.xlColumns, Type.Missing, Type.Missing, true, title, Type.Missing, Type.Missing, Type.Missing);
    
                chartPage.SetSourceData(chartRange, XlRowCol.xlColumns);
                Series oSeries1 = (Series)chartPage.SeriesCollection(2);
                oSeries1.ChartType = XlChartType.xlLine;
                oSeries1.Border.ColorIndex = 3;
                Series oSeries = (Series)chartPage.SeriesCollection(1);
                oSeries.ChartType = XlChartType.xlLine;
                oSeries.Border.ColorIndex = 5;
                if (isAllLine)
                {
                    Series oSeries2 = (Series)chartPage.SeriesCollection(3);
                    oSeries2.ChartType = XlChartType.xlLine;
                    oSeries2.Border.ColorIndex = 3;
                }
                chartPage.Legend.Delete();
                Axis xAxis = (Axis)chartPage.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                xAxis.Delete();
                var range = ws.Cells;
                range.WrapText = false;
            }
    
  • 相关阅读:
    FreeBSD 里面用 ADSL 来上网的设置
    功能齐全的DataGrid
    jsp中动态生成图像
    配置Struts应用解读Struts应用的两大配置文件
    spring 事务配置
    文件上传原理简单实现
    html中position两属性relative和absolute区别
    html 窗口参数详解
    hibernate联合主键的使用
    自动投票的一个脚本
  • 原文地址:https://www.cnblogs.com/zwgblogs/p/4175724.html
Copyright © 2020-2023  润新知