c#操作excel
怎么样用c#编写程序。实现excel插入一列，在网上找的代码excelhelper.cspublic void InsertColumns(int colIndex, int count, Excel.Worksheet workSheet)
{
try
{
Excel.Range range = (Excel.Range)workSheet.Columns[colIndex, this.missing];
for (int i = 0; i < count; i++)
{
range.Insert(Excel.XlDirection.xlToLeft);
}
}
catch (Exception e)
{
throw e;
}
}
以上代码不好用
望做过这方面的高手不吝赐教
qq：176099568 --------------------编程问答-------------------- 这么久怎么还没人回啊 --------------------编程问答-------------------- 这是我写的一个读写excel文件的类
你可以自己改一下功能都实现了
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Text.RegularExpressions;
namespace WindowsFormsApplication3
{
public class ConvertExecl
{
//创建Application对象
static Excel.Application _app = null;
static Workbooks _workbooks = null;
static _Workbook _workbook = null;
static Sheets _sheets = null;
static _Worksheet _workSheet = null;
object m_objOpt = System.Reflection.Missing.Value;
string _strResult = null;
string _strResult2 = null;
string _file = null;
public ConvertExecl()
{
}
~ConvertExecl()
{
close();
}
public void Init()
{
close();
_app = new Excel.ApplicationClass();
_workbooks = _app.Workbooks;
_app.Visible = false;
_app.UserControl = true;
_app.DisplayAlerts = false;
}
/// <summary>
/// 关闭Excle
/// </summary>
public void close()
{
if (_app == null) return;
_app.Workbooks.Close();
_app.Quit();
_app = null;
_sheets = null;
_workSheet = null;
_workbook = null;
_workbooks = null;
GC.Collect();
}
public bool open(string file)
{
try
{
Init();
_file = file;
_workbook = _workbooks.Add(file);
return true;
}
catch (System.Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
public void loadSheet()
{
int rowCount = 0;
int i = 0;
ItemManagement.Clear(ItemType.All);
foreach (_Worksheet wsheet in _workbook.Worksheets)
{
rowCount = wsheet.UsedRange.Rows.Count;
if (wsheet.Name.ToLower() == "c")
{
ItemC _item = null;
try
{
for (i = 3; i <= rowCount; i++)
{
_item = new ItemC();
_item.mark = ItemType.ItemC;
_item.id = wsheet.get_Range("A" + i, System.Reflection.Missing.Value).Text.ToString();
_item.name = wsheet.get_Range("B" + i, System.Reflection.Missing.Value).Text.ToString();
_item.gender = Convert.ToInt32(wsheet.get_Range("C" + i, System.Reflection.Missing.Value).Text.ToString());
_item.job = Convert.ToInt32(wsheet.get_Range("D" + i, System.Reflection.Missing.Value).Text.ToString());
_item.dir = Convert.ToInt32(wsheet.get_Range("E" + i, System.Reflection.Missing.Value).Text.ToString());
_item.action = Convert.ToInt32(wsheet.get_Range("F" + i, System.Reflection.Missing.Value).Text.ToString());
_item.actionList = wsheet.get_Range("G" + i, System.Reflection.Missing.Value).Text.ToString();
_item.delay = Convert.ToInt32(wsheet.get_Range("H" + i, System.Reflection.Missing.Value).Text.ToString());
_item.playCount = Convert.ToInt32(wsheet.get_Range("I" + i, System.Reflection.Missing.Value).Text.ToString());
ItemManagement.Add(_item);
}
}
catch (Exception)
{
_item = null;
}
}
}
}
public void writeSheet()
{
_workbook = _workbooks.Add(true);
_sheets = _workbook.Worksheets;
foreach (childClassItem item in Form1.Self.childClass)
{
_workSheet = (Worksheet)_sheets.Add(m_objOpt, m_objOpt, m_objOpt, m_objOpt);
_workSheet.Select(Type.Missing);
_workSheet.Name = item.flag;
int cols = 1;
int row = 3;
_workSheet.Cells[1, cols++] = "编号";
_workSheet.Cells[1, cols++] = "动画名";
SortedList<string, ItemBase> _list = ItemManagement.GetTable(item.mark);
switch (item.mark)
{
case ItemType.ItemC:
_workSheet.Cells[1, cols++] = "职业";
//_workSheet.Cells[2, cols] = "职业";
_workSheet.Cells[1, cols++] = "性别";
//_workSheet.Cells[2, cols] = "性别";
_workSheet.Cells[1, cols++] = "方向";
//_workSheet.Cells[2, cols] = "方向";
_workSheet.Cells[1, cols++] = "动作";
//_workSheet.Cells[2, cols] = "动作";
row = 3;
foreach (ItemC _item in _list.Values)
{
_workSheet.Cells[row, 1] = _item.id;
_workSheet.Cells[row, 2] = _item.name;
_workSheet.Cells[row, 3] = _item.gender;
_workSheet.Cells[row, 4] = _item.job;
_workSheet.Cells[row, 5] = _item.dir;
_workSheet.Cells[row, 6] = _item.action;
_workSheet.Cells[row, 7] = _item.actionList;
_workSheet.Cells[row, 8] = _item.delay;
_workSheet.Cells[row, 9] = _item.playCount;
row++;
}
break;
default:
break;
}
_workSheet.Cells[1, cols++] = "图片";
_workSheet.Cells[1, cols++] = "播放间隔";
_workSheet.Cells[1, cols++] = "播放次数";
}
_workSheet = (_Worksheet)_sheets.get_Item(_sheets.Count);
_workSheet.Delete();
_workSheet = null;
}
public bool saveSheet(string file)
{
try
{
_app.ActiveWorkbook.SaveAs(file, Excel.XlFileFormat.xlExcel7
, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
//System.Diagnostics.Process.Start("explorer.exe", Application.StartupPath.ToString());//打开目录
return true;
}
catch (System.Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
}
}
--------------------编程问答-------------------- 帮顶 --------------------编程问答-------------------- 插入一行或一列： ExcelApp.ActiveSheet.Rows[2].Insert;
参考
http://www.cnblogs.com/litianfei/archive/2008/03/21/1116906.html
--------------------编程问答-------------------- 不知道怎么添加列！！！只添加过行！！帮顶 --------------------编程问答-------------------- helper.workSheet.Columns[1].Insert
根本就没这个方法嘛 --------------------编程问答-------------------- 嘿嘿 --------------------编程问答-------------------- 自己再顶一下
没人会吗 --------------------编程问答-------------------- #region 导出到excel
/// <summary>
/// 导出到excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportExcel_Click(object sender, EventArgs e)
{
try
{
System.Data.DataTable dt = dtSource;
string TemplatePath = Server.MapPath(@"Template\Third.xls");
string ExportPath = Server.MapPath("") + @"\ExportReports";
if (!Directory.Exists(ExportPath))
{
Directory.CreateDirectory(ExportPath);
}
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(TemplatePath);
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
int i = 1;
for (; i <= dt.Rows.Count; i++)
{
int j = 0;
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["Month"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["PatientTypeName"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["OutCount"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["BedDayCount"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["AverageDay"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["TotalCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["BedCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["MedicineCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["CureCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["CheckUpCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["VerifyCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["PerAverageCharge"].ToString().Trim();
worksheet.Cells[i + 2, ++j] = dt.Rows[i - 1]["DayAverageCharge"].ToString().Trim();
}
string str = DateTime.Now.ToString("yyyyMMddHHmmss");
string strExportPath = ExportPath + @"\" + str + ".xls";
workbook.SaveAs(strExportPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.Quit();
app = null;
Response.Redirect("DownLoad.aspx?Path=" + strExportPath, false);
}
catch
{
Response.Write("<script>alert('导出失败！！')</script>");
return;
}
}
#endregion
我写的一个写入excel方法，你改下用吧
补充：.NET技术 , C#
