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);
}
