public CObjectExportColumn(C1FlexGrid i_fg, Excel.Worksheet i_objWorkSheet, int i_iGridCol, int i_iSheetCol, int i_iSheetStartRow)
 {
     m_fg = i_fg;
     m_objWorkSheet = i_objWorkSheet;
     m_iGridCol = i_iGridCol;
     m_iSheetCol = i_iSheetCol;
     m_iSheetStartRow = i_iSheetStartRow;
 }
        private void btnUploadMdr_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Title  = "Open Mdr File (Exel File)";
            ofd.Filter = "Excel Files|*.xls;*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                var excel = new Excel.Excel(ofd.FileName, 1);
                excel.Selectsheet("MDR");

                var rangeData = excel.ReadUsedRange_obj();

                excel.Close();

                //coloumns
                gridviewDocs.Columns.Clear();

                // Get the title values.
                for (int col = 5; col <= rangeData.GetUpperBound(1); col++)
                {
                    if (rangeData[2, col] != null)
                    {
                        string title = rangeData[2, col].ToString();
                        gridviewDocs.Columns.Add(title, title);
                    }
                }

                //remove first 4 cols and 8 rows from data range
                filteredarray = filterArray(rangeData, 8, 4);
                // get rows
                for (int i = 0; i < filteredarray.GetUpperBound(0); i++)
                {
                    object[] row = new object[filteredarray.GetUpperBound(0)];
                    for (int j = 0; j < filteredarray.GetUpperBound(1); j++)
                    {
                        row[j] = filteredarray[i, j];
                    }
                    if (row[0] != null)
                    {
                        gridviewDocs.Rows.Add(row);
                    }
                }
            }
        }
Beispiel #3
0
        public void Create(Chart chart, string title, Excel.DataTable table, string xColumn, string yColumn, string style, bool user3D)
        {
            chart.Palette = new Color[] { Color.FromArgb(49, 255, 49), Color.FromArgb(255, 255, 0), Color.FromArgb(255, 99, 49), Color.FromArgb(0, 156, 255),
            Color.FromArgb(255, 125, 49), Color.FromArgb(125, 255, 49), Color.FromArgb(0, 255, 49) };
            chart.Use3D = user3D;
            SeriesCollection mySC = getRandomData(table, xColumn, yColumn);
            if (string.IsNullOrEmpty(style) || style == "线形")
            {
                chart.Type = ChartType.Combo;
                mySC = getRandomData2(table, xColumn, yColumn);
            }
            else if (style == "柱形")
            {
                chart.Type = ChartType.Combo;
            }
            else if (style == "金字塔")
            {
                chart.Type = ChartType.MultipleGrouped;
                chart.DefaultSeries.Type = SeriesTypeMultiple.Pyramid;
            }
            else if (style == "圆锥")
            {
                chart.Type = ChartType.MultipleGrouped;
                chart.DefaultSeries.Type = SeriesTypeMultiple.Cone;
            }
            chart.Title = title;
            if (string.IsNullOrEmpty(style) || style == "线形")
            {
                chart.DefaultSeries.Type = SeriesType.Line;
            }

            chart.DefaultElement.ShowValue = true;
            chart.PieLabelMode = PieLabelMode.Outside;
            chart.ShadingEffectMode = ShadingEffectMode.Three;
            chart.NoDataLabel.Text = "没有数据显示";
            chart.SeriesCollection.Add(mySC);
        }
 public void SetOrientation(string fromAddress, string toAddress, Excel.XlOrientation orientation)
 {
     _excelRange = _excelSheet.get_Range(fromAddress, toAddress);
     _excelRange.Orientation = orientation;
 }
 public void SetAlign(string fromAddress, string toAddress, Excel.XlHAlign horizontalAlign, Excel.XlVAlign verticalAlign)
 {
     _excelRange = _excelSheet.get_Range(fromAddress, toAddress);
     _excelRange.HorizontalAlignment = horizontalAlign;
     _excelRange.VerticalAlignment = verticalAlign;
 }
 public static void KillExcelApplication(Excel.Application excelApp)
 {
     #region - release excel instance -
     IntPtr xlsApplicationProcessID = new IntPtr(0);
     GetWindowThreadProcessId(excelApp.Hwnd, ref xlsApplicationProcessID);
     System.Diagnostics.Process.GetProcessById(Convert.ToInt32(xlsApplicationProcessID.ToString())).Kill();
     #endregion
 }
Beispiel #7
0
        /// <summary>
        /// ������������ı߿��������Ҷ�Ϊ��ɫ�����߿�
        /// </summary>
        /// <param name="strStartCell">��ʼ��Ԫ���ʶ��</param>
        /// <param name="strEndCell">������Ԫ���ʶ��</param>
        public void SetBorderAll(string strStartCell, string strEndCell, Excel.XlBorderWeight xlEdgeWeight, Excel.XlBorderWeight xlInsideWeight)
        {
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = xlEdgeWeight;

            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = xlEdgeWeight;

            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = xlEdgeWeight;

            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = xlEdgeWeight;

            string iStart = "", iEnd = "";
            Regex r = new Regex(@"(\d+)", RegexOptions.IgnoreCase);
            System.Text.RegularExpressions.Match m = r.Match(strStartCell);
            if (m.Success)
            {
                iStart = m.Groups[1].ToString();
            }
            m = r.Match(strEndCell);
            if (m.Success)
            {
                iEnd = m.Groups[1].ToString();
            }
            if (iStart == iEnd)
                return;

            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = xlInsideWeight;

            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = xlInsideWeight;
        }
Beispiel #8
0
        private int DataTable2Excel(System.Data.DataTable dt, int totalCount, int currentCount, Dictionary<string, string> dicColumnNameMapping,
            object omissing, Excel.Workbook xlworkbook)
        {
            try
            {
                var tmpDataTable = dt.Copy();

                #region 表头
                if (dicColumnNameMapping != null)
                {
                    ResolveDatatableColumns(tmpDataTable, dicColumnNameMapping);
                }

                //var dr = tmpDataTable.NewRow();
                //for (int i = 0; i < tmpDataTable.Columns.Count; i++)
                //{
                //    dr[i] = tmpDataTable.Columns[i].ColumnName;
                //}
                //tmpDataTable.Rows.InsertAt(dr, 0);
                #endregion

                #region 快速导出数据

                int rowNum = tmpDataTable.Rows.Count;
                int columnNum = tmpDataTable.Columns.Count;
                int rowIndex = 1;
                int columnIndex = 0;

                Excel.Worksheet xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.Add(omissing, omissing, 1, omissing);
                xlworksheet.Name = tmpDataTable.TableName == null ? xlworksheet.Name : ResolveSheetName(tmpDataTable.TableName);
                int colnum = tmpDataTable.Columns.Count;
                Excel.Range r = xlworksheet.get_Range("A1", NumToExeclRowStr(colnum) + "1");

                object[] objHeader = new object[colnum];

                columnIndex = 0;//表头
                for (int j = 0; j < columnNum; j++)
                {
                    objHeader[columnIndex] = tmpDataTable.Columns[j].ColumnName;
                    columnIndex++;
                }
                r.Value2 = objHeader;

                //将DataTable中的数据导入Excel中
                for (int f = 0; f < rowNum; f++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        string danyinhao = "";//"'";
                        objHeader[columnIndex] = danyinhao + tmpDataTable.Rows[f][j].ToString();
                        columnIndex++;
                    }
                    r = xlworksheet.get_Range("A" + (f + 2), NumToExeclRowStr(colnum) + (f + 2));
                    r.Value2 = objHeader;
                    currentCount++;
                    AddProcess(currentCount, totalCount);
                }
                //r.EntireColumn.WrapText = true;
                r.EntireColumn.AutoFit();
                #endregion 快速导出数据
            }
            catch (Exception ex)
            {
                AddError(ex.Message);
            }
            return currentCount;
        }
 public CExportColumnFactory(C1FlexGrid i_fg, Excel.Worksheet i_objWorkSheet)
 {
     m_fg = i_fg;
     m_objWorkSheet = i_objWorkSheet;
 }
		private Excel.Workbook TryToOpenDocument(Excel.Workbooks workbooks, string filename, bool readOnly, int attempt)
		{
			try
			{
				object updateLinks = false;
				object vtReadOnly = readOnly;
				object format = Excel.XlFileFormat.xlWorkbookNormal;
				object vtMissing = System.Reflection.Missing.Value;

				return workbooks.Open(filename, updateLinks, vtReadOnly, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing);
			}
			catch (COMException e)
			{
				bool isIncorrectPassword = IsPasswordIncorrectError(e.ErrorCode);
				if ((attempt > 2) || !isIncorrectPassword)
				{
					if (isIncorrectPassword)
					{
						throw new UnauthorizedAccessException("One of the passwords specified was incorrect. Check that Caps Lock is not on", e);
					}
					throw;
				}

				return TryToOpenDocument(workbooks, filename, readOnly, attempt + 1);
			}
		}
		private void SaveWorkbook(Excel.Workbook workbook)
		{
			ApplicationControllerTrace trace = new ApplicationControllerTrace();
			try
			{
				if (!workbook.Saved)
					workbook.Save();
			}
			catch (Exception e)
			{
				trace.Log(e);
			}
		}