private void ArrayDataToGraphData(double[,] value, Word.Chart oChart, string[] rowHeader = null, string[] colHeader = null, bool firstColumnAsXAxis = true) { string data; string rc; string rHeader; string cHeader; Excel.Worksheet series = ((Excel.Workbook)oChart.ChartData.Workbook).Worksheets[1]; Excel.ListObject list = series.ListObjects[1]; string colIdx = firstColumnAsXAxis ? "B" : "A"; list.Resize(series.Cells.get_Range(colIdx + "1", ExcelReport.IntToLetter(value.GetLength(1) + 1) + (value.GetLength(0) + 1).ToString())); for (int i = 0; i < value.GetLength(0); i++) { rHeader = "A" + (i + 2).ToString(); if (rowHeader != null) { series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = rowHeader[i]; } else { series.Cells.get_Range(rHeader, _missing).FormulaR1C1 = (i).ToString(); } for (int j = 0; j < value.GetLength(1); j++) { data = value[i, j].ToString(); rc = ExcelReport.IntToLetter(j + 2) + (i + 2).ToString(); series.Cells.get_Range(rc, _missing).FormulaR1C1 = data; cHeader = ExcelReport.IntToLetter(j + 2) + "1"; if (i == 0) { if (colHeader != null) { series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = colHeader[j]; } else { series.Cells.get_Range(cHeader, _missing).FormulaR1C1 = "Column" + (j).ToString(); } } } } series.Activate(); ((Excel.Workbook)oChart.ChartData.Workbook).Close(); Thread.Sleep(5); }
/// <summary> /// 处理图表 /// </summary> /// <param name="element"></param> /// <returns></returns> private bool SetChart(WordElement element) { if (element.Range.InlineShapes != null && element.Range.InlineShapes.Count > 0) { List <Word.InlineShape> chartList = element.Range.InlineShapes.Cast <Word.InlineShape>().Where(m => m.Type == Word.WdInlineShapeType.wdInlineShapeChart).ToList(); string startWith = "chart_" + element.Level.ToString() + "_"; foreach (Word.InlineShape item in chartList) { Word.Chart chart = item.Chart; if (!string.IsNullOrEmpty(chart.ChartTitle.Text) && chart.ChartTitle.Text.StartsWith(startWith)) { WordElement chartElement = new WordElement(null, chart.ChartTitle.Text, element.DataRow); DataTable dataTable = dataSource.Tables[chartElement.TableIndex]; DataRow[] dataRows = dataTable.Select(chartElement.GroupByString); int columnCount = dataTable.Columns.Count; List <int> columns = new List <int>(); foreach (var dr in dataRows) { for (int i = chartElement.ColumnStart == -1 ? 0 : chartElement.ColumnStart - 1; i < (chartElement.ColumnEnd == -1 ? columnCount : chartElement.ColumnEnd); i++) { if (columns.Contains(i) || dr[i] == null || string.IsNullOrEmpty(dr[i].ToString())) { } else { columns.Add(i); } } } columns.Sort(); columnCount = columns.Count; int rowsCount = dataRows.Length; Word.ChartData chartData = chart.ChartData; //chartData.Activate(); //此处有个比较疑惑的问题, 不执行此条,生成的报告中的图表无法再次右键编辑数据. 执行后可以, 但有两个问题就是第一会弹出Excel框, 处理完后会自动关闭. 第二部分chart的数据range设置总不对 //不知道是不是版本的问题, 谁解决了分享一下,谢谢 Excel.Workbook dataWorkbook = (Excel.Workbook)chartData.Workbook; dataWorkbook.Application.Visible = false; Excel.Worksheet dataSheet = (Excel.Worksheet)dataWorkbook.Worksheets[1]; //设定范围 string a = (chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount) + "|" + columnCount; Console.WriteLine(a); Excel.Range tRange = dataSheet.Range["A1", dataSheet.Cells[(chartElement.ColumnNameForHead ? rowsCount + 1 : rowsCount), columnCount]]; Excel.ListObject tbl1 = dataSheet.ListObjects[1]; //dataSheet.ListObjects[1].Delete(); //想过重新删除再添加 这样 原有数据清掉了, 但觉得性能应该会有所下降 //Excel.ListObject tbl1 = dataSheet.ListObjects.AddEx(); tbl1.Resize(tRange); for (int j = 0; j < rowsCount; j++) { DataRow row = dataRows[j]; for (int k = 0; k < columnCount; k++) { dataSheet.Cells[j + 2, k + 1].FormulaR1C1 = row[columns[k]]; } } if (chartElement.ColumnNameForHead) { for (int k = 0; k < columns.Count; k++) { dataSheet.Cells[1, k + 1].FormulaR1C1 = dataTable.Columns[columns[k]].ColumnName; } } chart.ChartTitle.Text = chartElement.Name; //dataSheet.Application.Quit(); } } } return(true); }
public static bool LoadTextToTable(string txt, Microsoft.Office.Interop.Excel.ListObject target) { // King Sun 2014-09-13 when data is big, it's not possible to separate the first line (OutOfMemory issue), so start with the first line // jump out if empty string if (string.IsNullOrEmpty(txt)) { return(true); } // this function is to load text into a target range instead of using clipboard // to avoid problems in PutInClipboard() and Paste() // save current application settings //XlCalculation saveCalc; //bool saveDisplayAlerts = false; //bool saveScreenUpdating = false; //bool saveEnableEvents = false; //saveCalc = target.Application.Calculation; //saveDisplayAlerts = target.Application.DisplayAlerts; //saveScreenUpdating = target.Application.ScreenUpdating; //saveEnableEvents = target.Application.EnableEvents; //// disable application auto settings to improve performance //target.Application.DisplayAlerts = false; //target.Application.Calculation = XlCalculation.xlCalculationManual; //target.Application.ScreenUpdating = false; //target.Application.EnableEvents = false; // King Sun 2012-06-25 performance tuning, use Variant to store the data then push back to target data range directly // reference http://msdn.microsoft.com/en-us/library/ff726673.aspx#xlFasterVBA //Dim sh As Worksheet //Dim r As Long, c As Long //Set sh = target.Worksheet //r = target.Row() //c = target.Column() // split to lines // King Sun 2013-02-28 bug fix: remove Cr from the end of the line // lines = txt.Split("\n".ToCharArray()); string[] lines = System.Text.RegularExpressions.Regex.Split(txt, "\n"); int rowCnt = lines.Length - 1; if (rowCnt < 1) { return(true); } int maxRows = target.Range.Worksheet.Rows.Count - target.Range.Row; // Handles situations when the number of rows exceeds the Excel Limit (1048576 for Excel 2013) if (rowCnt > maxRows) { MessageBox.Show("Number of rows " + rowCnt.ToString() + " exceeds the Excel limit. Please be aware that data is incomplete."); rowCnt = maxRows; } // cols = lines[0].Split('\t'); int colCnt = target.ListColumns.Count; //// resize the table to fit rows and columns if (rowCnt == 1) { target.ListRows.AddEx(); } else { //Range rng = target.Range.Resize[rowCnt + 1, target.Range.Columns.Count]; Range cell1 = target.Range.Worksheet.Cells[RowIndex : target.Range.Row, ColumnIndex : target.Range.Column]; Range cell2 = target.Range.Worksheet.Cells[RowIndex : target.Range.Row + rowCnt, ColumnIndex : target.Range.Column + colCnt]; Range rng = target.Range.Worksheet.Range[cell1, cell2]; rng.ClearFormats(); target.Resize(rng); } char[] trimChars = new char[] { '\r', '\n' }; int rowPerProcess = ListObjectHelper.rowPerProcess; int loopCount = (int)Math.Ceiling((double)(rowCnt) / rowPerProcess) - 1; for (int l = 0; l <= loopCount; l++) { int StartRow = rowPerProcess * l + 1; int EndRow = StartRow + rowPerProcess - 1; if (EndRow > rowCnt) { EndRow = rowCnt; } int processRowCnt = EndRow - StartRow + 1; object[,] data = new object[processRowCnt, colCnt]; // loop through lines for (int r = StartRow; r <= EndRow; r++) { string[] d = lines[r].TrimEnd(trimChars).Split("\t".ToCharArray()); for (int c = 0; c < colCnt && c < d.Length; c++) { try { data[r - StartRow, c] = d[c]; } catch { Logging.debugLog("r:" + r.ToString() + " c:" + c.ToString()); } } } Range cell1 = target.DataBodyRange[RowIndex : StartRow, ColumnIndex : 1]; Range cell2 = target.DataBodyRange[RowIndex : EndRow, ColumnIndex : colCnt]; Range rng = target.Range.Worksheet.Range[cell1, cell2]; if (processRowCnt == 1 && colCnt == 1) { rng.Value = data[1, 1]; } else { rng.Value = data; } } //object[,] d = new object[lineCnt, colCnt]; ////ReDim d() //long i = 0; //int j = 0; //for (i = 0; i <= lineCnt - 1; i++) { // cols = lines[i].Split('\t'); // for (j = 0; j <= cols.Length - 1; j++) { // d[i, j] = cols[j]; // } //} //// write back data to the target range //target.DataBodyRange.Value = d; //// restore application auto settings //target.Application.DisplayAlerts = saveDisplayAlerts; //target.Application.Calculation = saveCalc; //target.Application.ScreenUpdating = saveScreenUpdating; //target.Application.EnableEvents = saveEnableEvents; return(false); }