asp.net(C#) 如何读写EXCEL单元格

GingerbreadMan 2008-06-06 03:08:47
B/S结构的程序。

如何操作格式比较复杂的EXCEL表(包含有合并的单元格)。

请教:如何对单元格操作?如何新建一个EXCEL表并填入内容?
...全文
603 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
winter116 2009-05-31
  • 打赏
  • 举报
回复
找不到类型或命名空间名称“b_excelStyle”(是否缺少 using 指令或程序集引用)

这个应该引用什么阿
xmq120 2008-06-09
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 GingerbreadMan 的回复:]
请教二楼:
如何才能在程序中声明
Excel.ApplicationClass,Excel.Workbook,Excel.Worksheet?
[/Quote]

在添加引用里面 选择 com组件 选择excel项即可
GingerbreadMan 2008-06-06
  • 打赏
  • 举报
回复
请教二楼:
如何才能在程序中声明
Excel.ApplicationClass,Excel.Workbook,Excel.Worksheet?
智能大石头 2008-06-06
  • 打赏
  • 举报
回复
楼上的例子都可以参考。


既然这样,我建议你还不如在Excel里面做宏算了
luckyboy101 2008-06-06
  • 打赏
  • 举报
回复
用excel模板,然后查找替换
踏雪听雨 2008-06-06
  • 打赏
  • 举报
回复
//生成图表
if (isChart && toChart)
{
Excel.Chart chart = (Excel.Chart)oBook.Charts.Add(Type.Missing, myWs, Type.Missing, Type.Missing);
chart.ChartWizard(
myWs.get_Range(myWs.Cells[1,1], myWs.Cells[1,1]), //图表数据区域
Excel.XlChartType.xlLine, //图表类型
Type.Missing,
Excel.XlRowCol.xlColumns, //数据是来自行还是来自列
1,
0,
true,
Title + "电子图表", //图表名称
"", //X轴标题
"", //Y轴标题
""
);

chart.Name = "电子图表";//sheet的名称

Excel.ChartGroup grp = (Excel.ChartGroup)chart.ChartGroups(1);
Excel.SeriesCollection sc = (Excel.SeriesCollection)grp.SeriesCollection(Type.Missing);

sc.NewSeries();
Excel.Series s1 = (Excel.Series)grp.SeriesCollection(1);
s1.XValues = "='" + Title + "'!R" + rowNum.ToString() + "C" + C_PosXV + ":R" + (rowNum + dt.Rows.Count - 1).ToString() + "C" + C_PosXV;

for (Int32 p = 0; p < C_PosV.Count; p++)
{
String SeriesName = colNameCn[(Convert.ToInt32(C_PosV[p]) - 1)];
if (p == 0)
{
s1.Name = SeriesName;
s1.Values = "='" + Title + "'!R" + rowNum.ToString() + "C" + C_PosV[p].ToString() + ":R" + (rowNum + dt.Rows.Count - 1).ToString() + "C" + C_PosV[p] ; //两种取得数据的方法myWs.get_Range(myWs.Cells[1, 2], myWs.Cells[5, 2]);
s1.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;//设置数据点格式是否带点标识
}
else
{
sc.NewSeries();
Excel.Series sn = (Excel.Series)grp.SeriesCollection(p + 1);
sn.Name = SeriesName;
sn.Values = "='" + Title + "'!R" + rowNum.ToString() + "C" + C_PosV[p].ToString() + ":R" + (rowNum + dt.Rows.Count - 1).ToString() + "C" + C_PosV[p].ToString();
try
{
sn.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone;
}
catch { /*当所有cell为空时,不能设置折线的样式*/}
}
}

Excel.Axis valueAxis = (Excel.Axis)excel.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = "值";

Excel.Axis categoryAxis = (Excel.Axis)excel.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
categoryAxis.CategoryType = Excel.XlCategoryType.xlCategoryScale;//X轴为分类模式
categoryAxis.HasTitle = true;
categoryAxis.AxisTitle.Text = "时间";
}

//生成统计表
if (toTotalSheet)
{
Excel.Worksheet newmyWs = (Excel.Worksheet)oBook.Sheets.Add(Type.Missing, myWs, Type.Missing, Type.Missing);
newmyWs.Name = "统计报表";
newmyWs.Move(oBook.Sheets[1], Type.Missing);

//生成表头
//第一行标题
r = newmyWs.get_Range(newmyWs.Cells[1, 1], newmyWs.Cells[1, C_PosV.Count + 1]);
r.MergeCells = true;
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
r.Font.Size = excelStyle.titleFontSize;
r.Font.Bold = excelStyle.titleBold;
r.Font.Name = "黑体";
r.Font.Color = excelStyle.titleColor.ToArgb();
r.Cells[1, 1] = Title + "统计表";

//第二行,时间
r = newmyWs.get_Range(newmyWs.Cells[2, 1], newmyWs.Cells[2, C_PosV.Count + 1]);
r.MergeCells = true;
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
r.Font.Size = excelStyle.contentFontSize;
r.Cells[1, 1] = "时间:" + total_TimeTitle + " " + dt.Rows[0][colNameEn[C_PosXV - 1, 0]].ToString() + "至" + dt.Rows[dt.Rows.Count - 1][colNameEn[C_PosXV - 1, 0]].ToString();

//第三行表头
Int32 xrNum = 3;
excel.Cells[xrNum, 1] = "统计方式";
for (Int32 ab = 0; ab < C_PosV.Count; ab++)
{
//统计方式 设备1 设备2。。。。。设备N
excel.Cells[xrNum, ab + 2] = colNameCn[Convert.ToInt32(C_PosV[ab]) - 1];
}
r = newmyWs.get_Range(newmyWs.Cells[xrNum, 1], newmyWs.Cells[xrNum, C_PosV.Count + 1]);
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
r.Font.Color = excelStyle.headColor.ToArgb();
r.Font.Size = excelStyle.contentFontSize;
r.ColumnWidth = excelStyle.cellWidth;
r.Borders.Value = Excel.XlBorderWeight.xlHairline;

//第四行开始数据区
xrNum = 4;
for (Int32 ac = 0; ac < formula.GetLength(0); ac++)
{
Int32 rowIntAa = 3 - xrNum;
Int32 rowIntAb = dt.Rows.Count + 2 - xrNum;

//中文描述
excel.Cells[xrNum, 1] = formula[ac, 0];
//英文公式
Int32 colPos = 2;
for (Int32 ad = 0; ad < C_PosV.Count; ad++)
{
Int32 colIntA = Convert.ToInt32(C_PosV[ad]) - (ad + 2);

String strB;
if (colIntA == 0)
{
strB = "C";
}
else
{
strB = "C[" + colIntA +"]";
}

excel.Cells[xrNum, colPos] = "=" + formula[ac, 1] + "('" + Title + "'!R[" + rowIntAa + "]" + strB + ":R[" + rowIntAb + "]" + strB + ")";
colPos++;
}
xrNum++;
}
r = newmyWs.get_Range(newmyWs.Cells[4, 1], newmyWs.Cells[xrNum - 1, C_PosV.Count + 1]);
r.Font.Size = excelStyle.contentFontSize;
r.Borders.Value = Excel.XlBorderWeight.xlHairline;
}

#region 保存回收垃圾

String path = mapPath + "xls\\" + fileName + ".xls";
try
{
oBook.SaveCopyAs(path);
oBook.Close(false, path, null);
}
catch
{
}
finally
{
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myWs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

oBook = null;
myWs = null;
excel = null;

System.GC.Collect();
}

#endregion

this.b_DownloadExcel(path);
}
#endregion
踏雪听雨 2008-06-06
  • 打赏
  • 举报
回复
#region 用EXCEL类导出EXCEL表格 -- 带图表 -- 带统计图 -- 未便用
/// <summary>
/// 将DataTable导出为规则的EXCEL表格//schema
/// 注意:
/// 1、DataTable的列必段与colName的中文列名与英文列名必须一一对应,且长度一至,否则出错
/// </summary>
/// <param name="dt">需导出的datatable</param>
/// <param name="colNameEn">表头列英文名 -- [字段名,是否数值输出]</param>
/// <param name="colNameCn">表头列中文名</param>
/// <param name="Title">Excel标题</param>
/// <param name="mapPath">保存路径</param>
/// <param name="fileName">文件名</param>
/// <param name="excelStyle">设置报表列头,标题的样式</param>
/// <param name="toChart">是否生成图表</param>
/// <param name="toTotalSheet">是否生成统计表</param>
/// <param name="formula">公式,[公式中文,公式英文]</param>
/// <param name="totalAndChart_Title">统计报表时间标题</param>
public void b_ExportExcel(DataTable dt, String[,] colNameEn, String[] colNameCn, String Title, String mapPath, String fileName, b_excelStyle excelStyle, Boolean toChart, Boolean toTotalSheet, String[,] formula, String total_TimeTitle)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook oBook = (Excel.Workbook)excel.Workbooks.Add(true);
Excel.Worksheet myWs = (Excel.Worksheet)oBook.ActiveSheet;
myWs.Name = Title;

excel.Visible = false;

//判断是否需要生成图表,根据XValues判断
Boolean isChart = false;

Int32 colCount = colNameEn.GetLength(0);//获取0维的元素个数

#region 生成表数据

//标题
Excel.Range r = myWs.get_Range(myWs.Cells[1, 1], myWs.Cells[1, colCount]);
r.MergeCells = true;
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
r.Font.Size = excelStyle.titleFontSize;
r.Font.Bold = excelStyle.titleBold;
r.Font.Name = "黑体";
r.Font.Color = excelStyle.titleColor.ToArgb();

excel.Cells[1, 1] = Title;

//表头
for (Int32 i = 1; i <= colCount; i++)
{
myWs.Cells[2, i] = colNameCn[i - 1];
r = myWs.get_Range(myWs.Cells[2, i], myWs.Cells[2, i]);
r.Font.Size = excelStyle.headFontSize;
r.Font.Bold = excelStyle.headBold;
r.Font.Color = excelStyle.headColor.ToArgb();
r.ColumnWidth = excelStyle.cellWidth;
}

//设置表头格式
r = myWs.get_Range(myWs.Cells[2, 1], myWs.Cells[2, colCount]);
r.Borders.Value = Excel.XlBorderWeight.xlHairline;
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
r.WrapText = true;

Int32 rowNum = 3;
for (Int32 m = 0; m < dt.Rows.Count; m++)
{
for (Int32 c = 0; c < colCount; c++)
{
r = myWs.get_Range(myWs.Cells[m + 3, c + 1], myWs.Cells[m + 3, c + 1]);
String strValue = dt.Rows[m][colNameEn[c, 0]].ToString().Trim();
try
{
Convert.ToDateTime(strValue);//判断是否为日期型
r.NumberFormatLocal = "@";
myWs.Cells[m + 3, c + 1] = strValue;
}
catch
{
myWs.Cells[m + 3, c + 1] = strValue;
}
//图表相关
pub.AxisStyle axis = (pub.AxisStyle)Enum.Parse(typeof(pub.AxisStyle), colNameEn[c, 1], true);
if (pub.AxisStyle.XValues == axis)
{
isChart = true;
r.NumberFormatLocal = excelStyle.cellNumberFormatLocal;
}
}
}
r = myWs.get_Range(myWs.Cells[3, 1], myWs.Cells[rowNum + (dt.Rows.Count - 1), colCount]);
r.Borders.Value = Excel.XlBorderWeight.xlHairline;
r.WrapText = true;

#endregion

//取出X轴,Y轴数据区域
Int32 C_PosXV = 0;
ArrayList C_PosV = new ArrayList();
for (Int32 u = 0; u < colNameEn.GetLength(0); u++)
{
pub.AxisStyle axis = (pub.AxisStyle)Enum.Parse(typeof(pub.AxisStyle), colNameEn[u, 1], true);
if (pub.AxisStyle.XValues == axis)
{
C_PosXV = u + 1;
}
if (pub.AxisStyle.Values == axis)
{
C_PosV.Add(u + 1);
}
}

62,025

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧