Example #1
0
        public static void CreateColumnName(DataTable dt, Microsoft.Office.Interop.Excel._Worksheet xlSheet, int pos_row)
        {
            int _ColumnsCount = dt.Columns.Count;
            //set thuoc tinh cho cac header
            Microsoft.Office.Interop.Excel.Range header = xlSheet.get_Range("A" + pos_row.ToString(), Convert.ToChar(_ColumnsCount + 65) + pos_row.ToString());
            header.Select();

            header.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            header.Font.Bold = true;
            header.Font.Size = 12;

            for (int i = 0; i < dt.Columns.Count; i++)
                xlSheet.Cells[pos_row, i + 1] = dt.Columns[i].ColumnName;
        }
        public void setCellValue(System.Collections.ArrayList value, Microsoft.Office.Interop.Excel.Worksheet wsheet, int rowindex, int colum, int count)
        {
            if (value.Count > 0)
            {
                string[,] strs = new string[value.Count, ((ArrayList)value[0]).Count];
                for (int i = 0; i < value.Count; i++)
                    for (int j = 0; j < ((ArrayList)value[i]).Count; j++)
                    {

                        strs[i, j] = (((ArrayList)value[i])[j]).ToString() == "0" ? "" : (((ArrayList)value[i])[j]).ToString();
                    }
                wsheet.get_Range(wsheet.Cells[rowindex, colum], wsheet.Cells[value.Count + rowindex - 1, ((ArrayList)value[0]).Count + colum - 1]).Value2 = strs;
            }
        }
Example #3
0
 /// <summary>
 /// 设置整个连续区域的字体颜色
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="clrColor">颜色</param>
 public void SetColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
 }
Example #4
0
        TryGetNamedRange
        (
            Microsoft.Office.Interop.Excel.Worksheet worksheet,
            String rangeName,
            out Range namedRange
        )
        {
            Debug.Assert(worksheet != null);
            Debug.Assert(!String.IsNullOrEmpty(rangeName));

            namedRange = null;

            try
            {
                namedRange = worksheet.get_Range(rangeName, Missing.Value);

                return (true);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                return (false);
            }
        }
Example #5
0
        public void AddTableToWorksheet(DataTable data, Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet)
        {
            // Add columns
            int iCol = 0;
            foreach (DataColumn c in data.Columns)
            {
                iCol++;
                xlsWorksheet.Cells[1, iCol] = c.ColumnName;
            }

            // Add rows
            int iRow = 0;
            foreach (DataRow r in data.Rows)
            {
                iRow++;

                for (int i = 1; i < data.Columns.Count + 1; i++)
                {
                    if (iRow == 1)
                    {
                        // Add the header the first time through 
                        xlsWorksheet.Cells[iRow, i] = data.Columns[i - 1].ColumnName;
                    }

                    if (r[1].ToString() != "")
                    {
                        xlsWorksheet.Cells[iRow + 1, i] = r[i - 1].ToString();
                    }
                }
            }

            var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            var range = xlsWorksheet.get_Range("A1", last);
            range.Columns.AutoFit();
        }
Example #6
0
 /// <summary>
 /// Write array to the worksheet Excel
 /// </summary>
 /// <param name="rows">The required amount of row into the worksheet </param>
 /// <param name="columns">The required amount of columns into the worksheet </param>
 /// <param name="worksheet">The worksheet in which data will be written </param>
 /// <param name="data"> The data to be written on the worksheet </param>
 private void WriteArrayRoadPavenment(int rows, int columns, Microsoft.Office.Interop.Excel.Worksheet worksheet, object[,] data)
 {
     var startRow = HEADERROWSRoadPavenment;
     var startCell = worksheet.Cells[startRow + 1, 1] as Microsoft.Office.Interop.Excel.Range; // At excel range start at the 1,1 not the 0,0
     var endCell = worksheet.Cells[rows + startRow, columns] as Microsoft.Office.Interop.Excel.Range;
     var writeRange = worksheet.get_Range(startCell, endCell);
     writeRange.Value2 = data;
 }
        private void spreadsheetDataSetTemplate(Microsoft.Office.Interop.Excel.Worksheet worksheet, string astrMethodName)
        {
            if(null != worksheet)
            {
                Microsoft.Office.Interop.Excel.Range rangeMethod = worksheet.get_Range("A1");
                Microsoft.Office.Interop.Excel.Range rangeMethodName = worksheet.get_Range("B1");
                Microsoft.Office.Interop.Excel.Range rangeErrors = worksheet.get_Range("A2");
                Microsoft.Office.Interop.Excel.Range rangeStart = worksheet.get_Range("A3");
                Microsoft.Office.Interop.Excel.Range rangeEnd = worksheet.get_Range("C3");
                Microsoft.Office.Interop.Excel.Range rangeDuraction = worksheet.get_Range("E3");

                rangeMethod.Value2 = "Method";
                rangeMethod.Font.Bold = true;
                rangeMethodName.Value2 = astrMethodName;

                rangeDuraction.Value2 = "Duration";
                rangeDuraction.Font.Bold = true;

                rangeStart.Value2 = "Start";
                rangeStart.Font.Bold = true;

                rangeEnd.Value2 = "End";
                rangeEnd.Font.Bold = true;

                rangeErrors.Value2 = "Errors";
                rangeErrors.Font.Bold = true;
            }
        }
        private int generateSpreadsheetColumnsRecursive(PropertyInfo[] apiProperties, Microsoft.Office.Interop.Excel.Worksheet awsWorksheet, int aiCurrentColumn)
        {
            int result = aiCurrentColumn;
            foreach (PropertyInfo currentProperty in apiProperties)
            {
                if (true == isPrimative(currentProperty.PropertyType))
                {
                    Microsoft.Office.Interop.Excel.Range rangeName = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 2));
                    Microsoft.Office.Interop.Excel.Range rangeType = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition + 1 - 2));

                    rangeName.Value2 = currentProperty.Name;
                    rangeName.Font.Bold = true;

                    rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString());
                    rangeType.Font.Italic = true;

                    aiCurrentColumn++;
                }
                else
                {
                    //Microsoft.Office.Interop.Excel.Range rangeName = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 3));
                    //Microsoft.Office.Interop.Excel.Range rangeType = awsWorksheet.get_Range(GetColumnLetter(aiCurrentColumn) + (iDataVerticalStartPosition - 4));

                    //rangeName.Value2 = currentProperty.Name;
                    //rangeName.Font.Bold = true;

                    //rangeType.Value2 = extractHumanReadableType(currentProperty.PropertyType.ToString());
                    //rangeType.Font.Italic = true;
                    aiCurrentColumn = generateSpreadsheetColumnsRecursive(currentProperty.PropertyType.GetProperties(), awsWorksheet, aiCurrentColumn);

                }
            }
            result = aiCurrentColumn;
            return (result);
        }
Example #9
0
 /// <summary>
 /// 设置行高
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="dblHeight">行高</param>
 public void SetRowHeight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight)
 {
     CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight;
 }
Example #10
0
 /// <summary>
 /// 设置连续区域的显示格式
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="strNF">如"#,##0.00"的显示格式</param>
 public void SetNumberFormat(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF)
 {
     CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
 }
Example #11
0
 /// <summary>
 /// 设置连续区域水平居右
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 public void SetHAlignRight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
 {
     CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
 }
Example #12
0
 /// <summary>
 /// 为单元格设置公式
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objCell">单元格</param>
 /// <param name="strFormula">公式</param>
 public void SetFormula(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strFormula)
 {
     CurSheet.get_Range(objCell, mValue).Formula = strFormula;
 }
Example #13
0
 /// <summary>
 /// 设置连续区域的字体大小
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="strStartCell">开始单元格</param>
 /// <param name="strEndCell">结束单元格</param>
 /// <param name="intFontSize">字体大小</param>
 public void SetFontSize(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
 }
Example #14
0
 /// <summary>
 /// 设置连续区域的字体名称
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
 public void SetFontName(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
 }
Example #15
0
 /// <summary>
 /// 设置列宽
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="dblWidth">宽度</param>
 public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth)
 {
     CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth;
 }
Example #16
0
 public static void Prc_Resize_Col(Microsoft.Office.Interop.Excel.Worksheet p_sheet, 
     int p_row_count,
     int p_col_count)
 {
     Microsoft.Office.Interop.Excel.Range _range;
     // Resize the columns
     _range = p_sheet.get_Range(p_sheet.Cells[1, 1],
                                p_sheet.Cells[p_row_count, p_col_count]);
     _range.EntireColumn.AutoFit();
 }
        private void callWebService(compiledAssembly compiledAsm, string astrMethod, List<Parameter> alsParameters, Microsoft.Office.Interop.Excel.Worksheet awsData, int aiStartRow)
        {
            CompilerResults compilerResult = compiledAsm.compilerResults as CompilerResults;

            int iErrorCount = 0;
            DateTime dtStart = DateTime.Now;
            DateTime dtEnd;

            if (null != compilerResult && false == string.IsNullOrEmpty(astrMethod))
            {
                var allTypes = compilerResult.CompiledAssembly.GetTypes();

                MethodInfo methodInfo = null; //compilerResult.GetType().GetMethod(astrMethod);

                foreach (var currentMethod in allTypes)
                {
                    if (null != currentMethod && null != currentMethod.BaseType)
                    {
                        if(null != (methodInfo = currentMethod.GetMethod(astrMethod)))
                        {
                            break;
                        }
                    }
                }

                var headerInfo = allTypes.Where(t => t.Name == "lws" || t.Name == "mws").First();

                object objHeader = System.Activator.CreateInstance(headerInfo);

                // this is the type of the final type in our heirachy where we plug in the spreadsheet values
                Type tpTypeToPopulate = null;

                // argumentType = getArgumentClassType(allTypes, astrMethod);

                List<Type> lsTypeHierarchy = getArgumentClassType(allTypes, astrMethod);

                List<object> lsCallObjects = new List<object>();
                // create the object hierachy, the first object in the list should be the one
                // supplied to the webservice call.  The last should be the one that we populate
                // with data from the spreadsheet
                createObjectHierachy(lsTypeHierarchy, lsCallObjects, 0);

                if(lsCallObjects.Count > 0)
                {
                    tpTypeToPopulate = lsTypeHierarchy.Last();
                    // object objObjectToSet = lsCallObjects.Last();
                    object objObjectToPassToCall = lsCallObjects.First();

                    foreach(object objObjectToSet in lsCallObjects)
                    {
                        tpTypeToPopulate = objObjectToSet.GetType();

                        for (int i = aiStartRow; i < giMaxRowPosition; i++)
                        {
                            bool bBlank = true;

                            foreach (Parameter currentParameter in alsParameters)
                            {
                                if (null != currentParameter)
                                {
                                    var spreadSheetValue = awsData.get_Range(currentParameter.Value + i).Value2;
                                    if (null != spreadSheetValue)
                                    {
                                        // retrieve the value from the spreadsheet
                                        string strValue = awsData.get_Range(currentParameter.Value + i).Value2.ToString();

                                        PropertyInfo currentProperty = tpTypeToPopulate.GetProperty(currentParameter.Name);

                                        if (null != currentProperty)
                                        {
                                            if (false == string.IsNullOrWhiteSpace(strValue))
                                            {
                                                bBlank = false;
                                            }

                                            object objValue = strValue;

                                            if (typeof(int) == currentProperty.PropertyType)
                                            {
                                                int iValue = 0;
                                                int.TryParse(strValue, out iValue);

                                                objValue = iValue;
                                            }
                                            else if (typeof(decimal) == currentProperty.PropertyType)
                                            {
                                                decimal decValue = 0;
                                                decimal.TryParse(strValue, out decValue);

                                                objValue = decValue;
                                            }
                                            else if (typeof(double) == currentProperty.PropertyType)
                                            {
                                                double dblValue = 0;
                                                double.TryParse(strValue, out dblValue);

                                                objValue = dblValue;
                                            }
                                            else if (typeof(DateTime) == currentProperty.PropertyType)
                                            {
                                                DateTime dtValue = DateTime.MinValue;

                                                DateTime.TryParse(strValue, out dtValue);

                                                objValue = dtValue;
                                            }

                                            // save the parameter
                                            tpTypeToPopulate.InvokeMember(currentParameter.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty, Type.DefaultBinder, objObjectToSet, new object[] { objValue });
                                        }
                                        else
                                        {
                                            // property doesn't exist
                                        }

                                    }
                                }
                            }
                            // if we have a blank line we should stop
                            if (true == bBlank)
                            {
                                break;
                            }
                            else
                            {
                                // call to the webservice here
                                try
                                {
                                    methodInfo.Invoke(compiledAsm.instantiatedObject, new object[] { objHeader, objObjectToPassToCall });
                                    awsData.get_Range(gstrResultColumn + i).Value2 = "OK";
                                }
                                catch (Exception ex)
                                {
                                    awsData.get_Range(gstrResultColumn + i).Value2 = ex;
                                    iErrorCount++;
                                }
                            }
                        }
                    }

                    dtEnd = DateTime.Now;

                    awsData.get_Range(gstrErrorCountCell).Value2 = iErrorCount;
                    awsData.get_Range(gstrStartTimeCell).Value2 = dtStart.ToString("yyyy-MM-dd HH:mm:ss");
                    awsData.get_Range(gstrEndTimeCell).Value2 = dtEnd.ToString("yyyy-MM-dd HH:mm:ss");
                    awsData.get_Range(gstrDurationCell).Value2 = (dtEnd - dtStart).TotalSeconds;
                }

            }
        }
Example #18
0
 /// <summary>
 /// 在指定单元格插入指定的值
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="Cell">单元格 如Cells[1,1]</param>
 /// <param name="objValue">文本、数字等值</param>
 public void WriteCell(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, object objValue)
 {
     CurSheet.get_Range(objCell, mValue).Value2 = objValue;
 }
        private List<Parameter> getParameterColumns(Microsoft.Office.Interop.Excel.Worksheet awsData, int aiParameterTitleStartX, int aiParameterTitleStartY)
        {
            List<Parameter> result = null, lsParameters = new System.Collections.Generic.List<Parameter>();

            int iXPos = aiParameterTitleStartX;
            int iYPos = aiParameterTitleStartY;

            for (int i = iXPos; i < giMaxColumnPosition; i++)
            {
                string strColumnLetter = GetColumnLetter(i);

                string strName = awsData.get_Range(strColumnLetter + (iYPos-2)).Value2;

                if(false == String.IsNullOrWhiteSpace(strName))
                {
                    lsParameters.Add(new Parameter() { Name = strName, Value = strColumnLetter });
                }
                else
                {
                    break;
                }
            }

            if (lsParameters.Count > 0)
            {
                result = lsParameters;
            }

            return (result);
        }
Example #20
0
 /// <summary>
 /// 在指定Range中插入指定的值
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="StartCell">开始单元格</param>
 /// <param name="EndCell">结束单元格</param>
 /// <param name="objValue">文本、数字等值</param>
 public void WriteRange(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
 }
Example #21
0
 /// <summary>
 /// 为单元格添加超级链接
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objCell">单元格</param>
 /// <param name="strAddress">链接地址</param>
 /// <param name="strTip">屏幕提示</param>
 /// <param name="strText">链接文本</param>
 public void AddHyperLink(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText)
 {
     CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText);
 }
Example #22
0
 /// <summary>
 /// 单元格自动换行
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 public void AutoWrapText(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
 {
     CurSheet.get_Range(objStartCell, objEndCell).WrapText = true;
 }
Example #23
0
        private void SetTitleAndListValues(Microsoft.Office.Interop.Excel.Worksheet sheet,int row, int col)
        {
            Microsoft.Office.Interop.Excel.Range range;

            // Set the title.
            range = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, col];

            // Get the values.
            // Find the last cell in the column.
            range = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[col, Type.Missing];
            Microsoft.Office.Interop.Excel.Range last_cell =
                range.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown);

            // Get a Range holding the values.
            Microsoft.Office.Interop.Excel.Range first_cell =
                (Microsoft.Office.Interop.Excel.Range)sheet.Cells[row + 1, col];
            Microsoft.Office.Interop.Excel.Range value_range =
                (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(first_cell, last_cell);

            // Get the values.
            object[,] range_values = (object[,])value_range.Value2;

            // Convert this into a 1-dimensional array.
            // Note that the Range's array has lower bounds 1.
            int num_items = range_values.GetUpperBound(0);
            //string[] values1 = new string[num_items];
            object[] values1 = new object[num_items];
            for (int i = 0; i < num_items; i++)
            {
                values1[i] = (object)range_values[i + 1, 1];
            }

            // Display the values in the ListBox.
            //lst.DataSource = values1;
        }
Example #24
0
        public static void Prc_Add_Body(Microsoft.Office.Interop.Excel.Worksheet p_sheet, 
            System.Data.DataTable p_dt)
        {
            Microsoft.Office.Interop.Excel.Range _range;
            /*
             * Modify by ManhTV3 on 18/04/2012
             * Content: Điều kiện kết xuất phụ lục
             * */
            int iRow = 0;
            if (p_sheet.Name.Substring(0,6).Equals("Phuluc"))
                iRow = 3;
            /*
             * Modify by ManhTV3 on 1/5/2012
             * Content: Điều kiện kết xuất báo cáo chuẩn hóa
             * */
            else if (p_sheet.Name.Equals("BaoCaoChuanHoa"))
                iRow = 1;

            int iCol = 0;
            foreach (DataRow r in p_dt.Rows)
            {
                iRow++;
                // add row
                iCol = 0;
                foreach (DataColumn c in p_dt.Columns)
                {
                    iCol++;
                    p_sheet.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }

            _range = p_sheet.get_Range(p_sheet.Cells[2, 1], p_sheet.Cells[iRow + 1, p_dt.Columns.Count]);
            _range.Font.Name = ".VnArial";
        }
Example #25
0
        /// <summary>
        /// Adds a small Infobox and a Validation with restriction (only these values will be selectable) to the specified cell.
        /// </summary>
        /// <param name="worksheet">The excel-sheet</param>
        /// <param name="rowNr">1-based row index of the cell that will contain the validation</param>
        /// <param name="columnNr">1-based column index of the cell that will contain the validation</param>
        /// <param name="title">Title of the Infobox</param>
        /// <param name="message">Message in the Infobox</param>
        /// <param name="validationValues">List of available values for selection of the cell. No other value, than this list is allowed to be used.</param>
        /// <exception cref="Exception">Thrown, if an error occurs, or the worksheet was null.</exception>
        public void AddDataValidation(Microsoft.Office.Interop.Excel.Worksheet worksheet, Microsoft.Office.Interop.Excel.Worksheet validationWorksheet, string col, int row,
            string title, string message, List<string> validationValues, CultureInfo currentCulture)
        {
            if (validationValues == null || validationValues.Count == 0)
                return;
            //If the message-string is too long (more than 255 characters, prune it)
            if (message.Length > 255)
                message = message.Substring(0, 254);

            try
            {
                if (!validationRanges.ContainsKey(col))
                {
                    var valStart = validationRow;
                    foreach (var val in validationValues)
                    {
                        validationWorksheet.Cells[validationRow, "A"] = val;
                        validationRow++;
                    }
                    validationRanges.Add(col, string.Format("={0}!$A{1}:$A{2}", validationSheetName, valStart, validationRow - 1));
                }
                #region old validation
                //The validation requires a ';'-separated list of values, that goes as the restrictions-parameter.
                //Fold the list, so you can add it as restriction. (Result is "Value1;Value2;Value3")
                //If you use another separation-character (e.g in US) change the ; appropriately (e.g. to the ,)

                //string values = "";
                //if (currentCulture.TwoLetterISOLanguageName == "en")
                //    values = string.Join(",", validationValues.ToArray());
                //else
                //    values = string.Join(";", validationValues.ToArray());
                #endregion

                //Select the specified cell
                Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range)worksheet.get_Range(col + row, col + row);
                //Delete any previous validation
                cell.Validation.Delete();
                //Add the validation, that only allowes selection of provided values.
                cell.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
                    Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
                    Type.Missing,
                    validationRanges[col], Type.Missing);
                cell.Validation.IgnoreBlank = true;
                //Optional put a message there
                cell.Validation.InputTitle = title;
                cell.Validation.InputMessage = message;

            }
            catch (Exception exception)
            {
                //This part should not be reached, but is used for stability-reasons
                throw new Exception(String.Format("Error when adding a Validation with restriction to the specified cell Row:{0}, Column:{1}, Message: {2}", row, col, message), exception);

            }
        }
Example #26
0
        public static void Prc_Add_Header(Microsoft.Office.Interop.Excel.Worksheet p_sheet, 
            System.Data.DataTable p_dt)
        {
            Microsoft.Office.Interop.Excel.Range _range;
            int iCol = 0;
            foreach (DataColumn c in p_dt.Columns)
            {
                iCol++;
                p_sheet.Cells[1, iCol] = c.ColumnName;
            }

            _range = p_sheet.get_Range(p_sheet.Cells[1, 1], p_sheet.Cells[1, p_dt.Columns.Count]);
            _range.Font.Bold = true;
        }
Example #27
0
        TryGetContiguousValuesInRowOrColumn
        (
            Microsoft.Office.Interop.Excel.Worksheet worksheet,
            Int32 rowOneBased,
            Int32 columnOneBased,
            Boolean inRow,
            out Object[,] values
        )
        {
            Debug.Assert(worksheet != null);
            Debug.Assert(rowOneBased >= 1);
            Debug.Assert(columnOneBased >= 1);

            values = null;

            Range oFirstCell = (Range)worksheet.Cells[rowOneBased, columnOneBased];

            String sTemp;

            if (!ExcelUtil.TryGetNonEmptyStringFromCell(oFirstCell, out sTemp))
            {
                return (false);
            }

            Range oNextCell = (Range)worksheet.Cells[
                rowOneBased + (inRow ? 0 : 1),
                columnOneBased + (inRow ? 1 : 0)
                ];

            Range oLastCell;

            // If the next cell is empty, Range.get_End() can't be used because it
            // jumps beyond the empty cell, possibly to the end of the worksheet.

            if (!ExcelUtil.TryGetNonEmptyStringFromCell(oNextCell, out sTemp))
            {
                oLastCell = oFirstCell;
            }
            else
            {
                oLastCell = oFirstCell.get_End(
                    inRow ? XlDirection.xlToRight : XlDirection.xlDown);
            }

            values = ExcelUtil.GetRangeValues(
                worksheet.get_Range(oFirstCell, oLastCell));

            return (true);
        }
Example #28
0
 public static void Prc_Format_Col(Microsoft.Office.Interop.Excel.Worksheet p_sheet, 
     int p_row_count,
     int p_col_count)
 {
     Microsoft.Office.Interop.Excel.Range _range;
     // Format Column
     _range = p_sheet.get_Range(p_sheet.Cells[1, 1],
                                p_sheet.Cells[p_row_count, p_col_count]);
     _range.EntireColumn.NumberFormat = "@";
 }
Example #29
0
        GetValuesInColumn
        (
            Microsoft.Office.Interop.Excel.Worksheet worksheet,
            Int32 firstRowOneBased,
            Int32 columnOneBased,
            Int32 rows
        )
        {
            Debug.Assert(worksheet != null);
            Debug.Assert(firstRowOneBased >= 1);
            Debug.Assert(columnOneBased >= 1);
            Debug.Assert(rows >= 1);

            Range oRange = (Range)worksheet.get_Range(

                (Range)worksheet.Cells[firstRowOneBased, columnOneBased],

                (Range)worksheet.Cells[firstRowOneBased + rows - 1,
                    columnOneBased]
                );

            return (ExcelUtil.GetRangeValues(oRange));
        }
Example #30
0
        /// <summary>
        /// 设置连续区域的边框:上下左右都为黑色连续边框
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetBorderAll(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
        }