Excel Interop - insert&按行添加数据 [英] Excel Interop - insert & add data by row
本文介绍了Excel Interop - insert&按行添加数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
到目前为止能够创建空行,但是我有一段时间试图找出如何将Range.Value设置为String类型的数组
插入行的方法:
private void shiftRows(int from,int numberof)
{
from ++;
范围r = oXL.get_Range(A+ from.ToString(),A+ from.ToString())。 (int i = 0; i< numberof; i ++)
r.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown);
}
//所以这会将下面的行数移动次数。
此方法目前是我被困在...上的一个数组插入新行一行一行
public void inputRowData(string [] data,int rds)
{
int bestRow = getRowByRDS(rds);
string val = getValueOfCell(bestRow,6);
if(val == null || val.Equals())
{
shiftRows(bestRow,data.Length);
string [] formatedData = formatOutput(bestRow,data); (int i = 0; i< formatedData.Length; i ++)
{
范围r = oSheet.get_Range((bestRow + i).ToString()+:+ (bestRow + i).ToString());
r.set_Value(formatedData [i] .Split('\t'));
//尝试过r.Value = formatedData [i] .Split('\t')
// formatedData是一个字符串数组,包含每个单元格的数据, tab
}
}
else
{
Console.WriteLine(Line has some information already,skip one more more);
shiftRows(bestRow,data.Length + 1);
}
}
解决方案
我强烈建议您:
- 不插入行,但只写空行(安全和性能)
- 设置一个大的数组对象,只做一个写入excel(性能)
示例(我保持换档,但你应该真的摆脱它):
public void inputRowData(string [] data,int rds)
{
int bestRow = getRowByRDS(rds);
string val = getValueOfCell(bestRow,6);
if(val == null || val.Equals())
{
shiftRows(bestRow,data.Length);
string [] formatedData = formatOutput(bestRow,data);
//将formated数据转换为string [,]
var string [] [] splitedData = formatedData.Select(s => s.Split('\t'))ToArray();
var colCount = splitedData.Max(r => r.Lenght);
var excelData = new string [splitedData.Length,colCount]
for(int i = 0; i< splitedData.Length; i ++)
{
for(int j = 0; j< splitedData [i] .Length; j ++)
{
excelData [i,j] = splitedData [i] [j];
}
}
oSheet.get_Range(A+ bestRow.ToString())。调整大小(splitedData.Length,colCount).Value = excelData;
}
else
{
Console.WriteLine(Line has some information already,skip one more more);
shiftRows(bestRow,data.Length + 1);
}
}
Hey guys I need to insert empty rows below a certain row in an excel and then add data into those empty rows I inserted...
So far I am able to create empty rows but I am having a hell of a time trying to figure out how to set Range.Value to an array of type String
Method for inserting Rows:
private void shiftRows(int from, int numberof)
{
from++;
Range r = oXL.get_Range("A" + from.ToString(), "A" + from.ToString()).EntireRow;
for (int i = 0; i < numberof; i++)
r.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown);
}
// so this would shift the below rows by numberof times.
This method is currently what I am stuck on... which is inserting an array into the new rows one row at a time
public void inputRowData(string[] data, int rds)
{
int bestRow = getRowByRDS(rds);
string val = getValueOfCell(bestRow, 6);
if (val == null || val.Equals(""))
{
shiftRows(bestRow, data.Length);
string[] formatedData = formatOutput(bestRow, data);
for (int i = 0; i < formatedData.Length; i++)
{
Range r = oSheet.get_Range((bestRow + i).ToString() + ":" + (bestRow + i).ToString());
r.set_Value(formatedData[i].Split('\t'));
// have tried r.Value = formatedData[i].Split('\t')
// formatedData is an array of string which contains data for each cell seperated by a tab
}
}
else
{
Console.WriteLine("Line has some information already, skipping 1 more");
shiftRows(bestRow, data.Length + 1);
}
}
解决方案
I strongly advise you:
- NOT to insert rows but just write empty row instead (safety and performance)
- to set a big array object and do only ONE write in excel (performance)
example (i kept the shiftrows but you should really get rid of it):
public void inputRowData(string[] data, int rds)
{
int bestRow = getRowByRDS(rds);
string val = getValueOfCell(bestRow, 6);
if (val == null || val.Equals(""))
{
shiftRows(bestRow, data.Length);
string[] formatedData = formatOutput(bestRow, data);
// transform formated data into string[,]
var string[][] splitedData = formatedData.Select(s => s.Split('\t')).ToArray();
var colCount = splitedData.Max(r => r.Lenght);
var excelData = new string[splitedData.Length, colCount]
for (int i = 0; i < splitedData.Length; i++)
{
for (int j = 0; j < splitedData[i].Length; j++)
{
excelData[i,j] = splitedData[i][j];
}
}
oSheet.get_Range("A" + bestRow.ToString()).Resize(splitedData.Length, colCount).Value = excelData;
}
else
{
Console.WriteLine("Line has some information already, skipping 1 more");
shiftRows(bestRow, data.Length + 1);
}
}
这篇关于Excel Interop - insert&按行添加数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文