MircosoftExcel.ApplicationClass app = new MircosoftExcel.ApplicationClass();
MircosoftExcel.Workbook workbook = null;
MircosoftExcel.Worksheet worksheet = null; try
{
string fileName = "ExportModle.xls";
string filePath = System.AppDomain.CurrentDomain.BaseDirectory;
filePath = filePath.Substring(0, filePath.LastIndexOf("bin")) + "Orders\\";
string strPathName = filePath + fileName;
workbook = app.Workbooks.Open(strPathName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (MircosoftExcel.Worksheet)workbook.Sheets[1]; #region 赋值
worksheet.Cells[3, "B"] = ds.Tables[1].Rows[0]["ShortName"].ToString();
worksheet.Cells[4, "B"] = ds.Tables[1].Rows[0]["Address"].ToString();
worksheet.Cells[3, "F"] = "联系人:" + ds.Tables[1].Rows[0]["deptname"].ToString();
worksheet.Cells[3, "H"] = "联系方式:" + ds.Tables[1].Rows[0]["tel1"].ToString();
worksheet.Cells[3, "K"] = DateTime.Now.ToString("yyyy-MM-dd"); int RowIndex = 6;
//循环插入数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Model,ProductNo,ProductName,EncapDiamter,EnCapLength,Length,Amount,OrdersNo
//机型 6,A 产品编号B,胶辊名称C,包胶外径D,包胶长度E,总长F,数量G,单价H,合计金额I,订单编号J,价格来源K,备注L
worksheet.Cells[RowIndex, "A"] = ds.Tables[0].Rows[i]["Model"].ToString();
worksheet.Cells[RowIndex, "B"] = ds.Tables[0].Rows[i]["ProductNo"].ToString();
worksheet.Cells[RowIndex, "C"] = ds.Tables[0].Rows[i]["ProductName"].ToString();
worksheet.Cells[RowIndex, "D"] = ds.Tables[0].Rows[i]["EncapDiamter"].ToString();
worksheet.Cells[RowIndex, "E"] = ds.Tables[0].Rows[i]["EnCapLength"].ToString();
worksheet.Cells[RowIndex, "F"] = ds.Tables[0].Rows[i]["Length"].ToString();
worksheet.Cells[RowIndex, "G"] = ds.Tables[0].Rows[i]["Amount"].ToString();
//worksheet.Cells[RowIndex, "H"] = ds.Tables[0].Rows[i][""].ToString();
//worksheet.Cells[RowIndex, "I"] = ds.Tables[0].Rows[i][""].ToString();
worksheet.Cells[RowIndex, "J"] = ds.Tables[0].Rows[i]["OrdersNo"].ToString();
RowIndex++;
}
#endregion SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//设置文件类型
saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls";
//saveFileDialog1.FilterIndex = 2;
saveFileDialog1.CreatePrompt = true;
saveFileDialog1.Title = "导出文件保存路径";
//保存对话框是否记忆上次打开的目录
//saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//m_SaveFile = saveFileDialog1.FileName; //获得文件路径
string localFilePath = saveFileDialog1.FileName.ToString();
//获取文件名,不带路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
//获取文件路径,不带文件名
string FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
string newFileName = FilePath+"\\" + fileNameExt;
m_SaveFile = newFileName; workbook.SaveAs(m_SaveFile, missing, missing, missing, missing, missing, MircosoftExcel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); }
#region 释放 workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
workbook = null;
KillSpecialExcel(app);
//杀死进程
app = null;
#endregion
以上是我的插入行的代码 我的Excel是有模板的的当RowIndex=6的时候插入新行问插入新行怎么做
MircosoftExcel.Workbook workbook = null;
MircosoftExcel.Worksheet worksheet = null; try
{
string fileName = "ExportModle.xls";
string filePath = System.AppDomain.CurrentDomain.BaseDirectory;
filePath = filePath.Substring(0, filePath.LastIndexOf("bin")) + "Orders\\";
string strPathName = filePath + fileName;
workbook = app.Workbooks.Open(strPathName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (MircosoftExcel.Worksheet)workbook.Sheets[1]; #region 赋值
worksheet.Cells[3, "B"] = ds.Tables[1].Rows[0]["ShortName"].ToString();
worksheet.Cells[4, "B"] = ds.Tables[1].Rows[0]["Address"].ToString();
worksheet.Cells[3, "F"] = "联系人:" + ds.Tables[1].Rows[0]["deptname"].ToString();
worksheet.Cells[3, "H"] = "联系方式:" + ds.Tables[1].Rows[0]["tel1"].ToString();
worksheet.Cells[3, "K"] = DateTime.Now.ToString("yyyy-MM-dd"); int RowIndex = 6;
//循环插入数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Model,ProductNo,ProductName,EncapDiamter,EnCapLength,Length,Amount,OrdersNo
//机型 6,A 产品编号B,胶辊名称C,包胶外径D,包胶长度E,总长F,数量G,单价H,合计金额I,订单编号J,价格来源K,备注L
worksheet.Cells[RowIndex, "A"] = ds.Tables[0].Rows[i]["Model"].ToString();
worksheet.Cells[RowIndex, "B"] = ds.Tables[0].Rows[i]["ProductNo"].ToString();
worksheet.Cells[RowIndex, "C"] = ds.Tables[0].Rows[i]["ProductName"].ToString();
worksheet.Cells[RowIndex, "D"] = ds.Tables[0].Rows[i]["EncapDiamter"].ToString();
worksheet.Cells[RowIndex, "E"] = ds.Tables[0].Rows[i]["EnCapLength"].ToString();
worksheet.Cells[RowIndex, "F"] = ds.Tables[0].Rows[i]["Length"].ToString();
worksheet.Cells[RowIndex, "G"] = ds.Tables[0].Rows[i]["Amount"].ToString();
//worksheet.Cells[RowIndex, "H"] = ds.Tables[0].Rows[i][""].ToString();
//worksheet.Cells[RowIndex, "I"] = ds.Tables[0].Rows[i][""].ToString();
worksheet.Cells[RowIndex, "J"] = ds.Tables[0].Rows[i]["OrdersNo"].ToString();
RowIndex++;
}
#endregion SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//设置文件类型
saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls";
//saveFileDialog1.FilterIndex = 2;
saveFileDialog1.CreatePrompt = true;
saveFileDialog1.Title = "导出文件保存路径";
//保存对话框是否记忆上次打开的目录
//saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//m_SaveFile = saveFileDialog1.FileName; //获得文件路径
string localFilePath = saveFileDialog1.FileName.ToString();
//获取文件名,不带路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
//获取文件路径,不带文件名
string FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
string newFileName = FilePath+"\\" + fileNameExt;
m_SaveFile = newFileName; workbook.SaveAs(m_SaveFile, missing, missing, missing, missing, missing, MircosoftExcel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); }
#region 释放 workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
workbook = null;
KillSpecialExcel(app);
//杀死进程
app = null;
#endregion
以上是我的插入行的代码 我的Excel是有模板的的当RowIndex=6的时候插入新行问插入新行怎么做
worksheet.Cells[RowIndex, "A"]对应的第几行第一列
前面好像有给第一列赋值的语言
RowIndex++后就会自动向后面的行写数据进去,不用插入新行吧
收货人签字: 客服主管签字: 财务主管签字: 发货人签字:
这行要一直在最底下,不能覆盖了,所以在当行循环到第10行的时候插入新行,来保持他们在最底下
worksheet.Cells[1,"A"].Insert Shift:=xlDown
worksheet.Cells[1,"A"].Insert Shift:=xlDown 还有这句话不好用
MircosoftExcel.Range range;
int RowIndex = 6;
//循环插入数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (RowIndex > 9)
{
range = (MircosoftExcel.Range)worksheet.Rows[RowIndex, missing];
range.Insert(MircosoftExcel.XlInsertShiftDirection.xlShiftDown, missing);
}