怎么在此代码中实现dadatable导出Excel时,自动调整列宽和行宽?此方法是将数据写入一个空模板,空模板小弟我默认为设置文本,因为导出的数据的列是可变的
如何在此代码中实现dadatable导出Excel时,自动调整列宽和行宽?此方法是将数据写入一个空模板,空模板我默认为设置文本,因为导出的数据的列是可变的
/// <summary>
/// Excel数据批量写入
/// </summary>
/// <param name="dtTmp">数据源(只能为DataTable)</param>
/// <param name="shtObj">工作薄对象</param>
/// <param name="hasFieldHeader">是否写入表头数据</param>
public void WriteDataByBatch(DataTable dtTmp, Excel.Worksheet shtObj, bool hasFieldHeader)
{
object[,] dataArray = null;
if (hasFieldHeader)
{
dataArray = new object[dtTmp.Rows.Count + 1, dtTmp.Columns.Count];
}
else
{
dataArray = new object[dtTmp.Rows.Count, dtTmp.Columns.Count];
}
if (hasFieldHeader)
{
for (int j = 0; j < dtTmp.Columns.Count; j++)
{
dataArray[0, j] = dtTmp.Columns[j].ColumnName;
}
}
for (int i = 0; i < dtTmp.Rows.Count; i++)
{
for (int j = 0; j < dtTmp.Columns.Count; j++)
{
dataArray[i + (hasFieldHeader ? 1 : 0), j] = dtTmp.Rows[i][j];
}
}
string startRange = hasFieldHeader ? "A1" : "A2";
string endRange = GetColumnName(dataArray.GetUpperBound(1) + 1) + (dataArray.GetUpperBound(0) + (hasFieldHeader ? 1 : 2)).ToString();
shtObj.get_Range(startRange + ":" + endRange, System.Reflection.Missing.Value).Value2 = dataArray;
}
------解决方案--------------------
shtObj.Rows.AutoFit();
shtObj.Columns.AutoFit();
/// <summary>
/// Excel数据批量写入
/// </summary>
/// <param name="dtTmp">数据源(只能为DataTable)</param>
/// <param name="shtObj">工作薄对象</param>
/// <param name="hasFieldHeader">是否写入表头数据</param>
public void WriteDataByBatch(DataTable dtTmp, Excel.Worksheet shtObj, bool hasFieldHeader)
{
object[,] dataArray = null;
if (hasFieldHeader)
{
dataArray = new object[dtTmp.Rows.Count + 1, dtTmp.Columns.Count];
}
else
{
dataArray = new object[dtTmp.Rows.Count, dtTmp.Columns.Count];
}
if (hasFieldHeader)
{
for (int j = 0; j < dtTmp.Columns.Count; j++)
{
dataArray[0, j] = dtTmp.Columns[j].ColumnName;
}
}
for (int i = 0; i < dtTmp.Rows.Count; i++)
{
for (int j = 0; j < dtTmp.Columns.Count; j++)
{
dataArray[i + (hasFieldHeader ? 1 : 0), j] = dtTmp.Rows[i][j];
}
}
string startRange = hasFieldHeader ? "A1" : "A2";
string endRange = GetColumnName(dataArray.GetUpperBound(1) + 1) + (dataArray.GetUpperBound(0) + (hasFieldHeader ? 1 : 2)).ToString();
shtObj.get_Range(startRange + ":" + endRange, System.Reflection.Missing.Value).Value2 = dataArray;
}
------解决方案--------------------
shtObj.Rows.AutoFit();
shtObj.Columns.AutoFit();