导入EXCEL

问:
代码如下:
System.Data.DataTable dt = DbProvider.Instance().GetListComOrder("0","cAccount");
StringWriter sw=new StringWriter();
sw.WriteLine("iOrderID/tcOrderName");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr["iOrderID"]+"/t"+dr["cOrderName"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();

导是没问题,主要是导到指定的位置,如要求数据从4行,5列开始写入怎么实现呀,给个Datagrid,域者datalist的这方面的例子也行.感谢..

回答1:
excel.Cells[rowIndex,colIndex],你可以随意改变rowIndex, colIndex的，你一个单元格一个单元格的写，想写哪里就写哪里。 http://www.programfan.com/blog/article.asp?id=10595

回答2:
请问,如果导的是datalist应该怎么实现指定位置导入excel呀!!

回答3:
用一个循环，idt.Rows.Count和dt.Cloumns.Count,
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == 2)
{
worksheet.Cells.NumberFormatLocal = "@";
}
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
//处理windows消息
System.Windows.Forms.Application.DoEvents();
}

r和ｉ自己定，想取ＤＡＴＡＧＲＩＤ的那一个单元格都可以定义

回答4:
帮LZ顶

回答5:
try
...{ string filename;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.Filter="excel文档(*.xls)|*.xls";
//sfd.OpenFile();
sfd.ShowDialog();
if (sfd.FileName != "")
...{
if(sfd.FileName.LastIndexOf(".xls")<=0)
...{
sfd.FileName=sfd.FileName+".xls";
}
filename=sfd.FileName;
if(System.IO.File.Exists(filename))
...{
System.IO.File.Delete(filename);
}
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
if (xlApp == null)
...{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
DataTable dt1 = (DataTable)dataGrid2.DataSource;
float percent=0;
long rowRead=0;
if(dt1==null)
...{
MessageBox.Show("1");
return;
}
long totalCount=dt1.Rows.Count;
this.progressBar1.Visible=true;
for (int i = 0; i < dt1.Rows.Count; i++)
...{
this.progressBar1.Value=i;
for (int j = 0; j < dt1.Columns.Count; j++)
...{
if (i == 0)
...{
worksheet.Cells[1, j + 1] = dt1.Columns[j].ColumnName;
}
worksheet.Cells[i + 2, j + 1] = dt1.Rows[i][j].ToString();
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;
this.progressBar1.Text= "正在导出数据["+ percent.ToString("0.00") +"%]...";
}
this.progressBar1.Visible=false;
workbook.Saved = true;
workbook.SaveCopyAs(filename);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
MessageBox.Show("导出Excel完成!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
//button2_Click(null,null);

}
}
catch(Exception ex)
...{
MessageBox.Show("导出Excel失败!"+ex.Message,"错误",MessageBoxButtons.OK,MessageBoxIcon.Information);
}

 

 

public void  TableToExcel(string excel_path,string produce_name,string date, DataTable table)//把数据导入到指定目录的excel中

        {

            EventLog log1 = new EventLog();

            log1.Source = "excel";

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

          

            try

            {

               

                Microsoft.Office.Interop.Excel.Workbooks wbs = excel.Workbooks;

                Microsoft.Office.Interop.Excel.Workbook workbook = wbs.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

 

                worksheet.Cells.HorizontalAlignment = -4108; //-4108居中-4152居右 -4131 居左

                worksheet.Name = "SQL Results";

 

                int rowIndex = 1;

                int colIndex = 0;

 

 

                //将所得到的表的列名,赋值给单元格

                foreach (DataColumn col in table.Columns)

                {

                    colIndex++;

                    excel.Cells[1, colIndex] = col.ColumnName;

                    excel.Cells.ColumnWidth = 20;

                }

             

 

                //同样方法处理数据

                foreach (DataRow row in table.Rows)

                {

 

                    rowIndex++;

                    colIndex = 0;

                    foreach (DataColumn col in table.Columns)

                    {

                        colIndex++;

                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();

                        excel.Cells.ColumnWidth = 20;

                    }

                }

 

                string mm = excel_path + '//' + date + "_" + produce_name + ".xls";

                excel.ActiveWorkbook.SaveCopyAs(mm);

                workbook.Saved = true;

              

            }

            catch (Exception ex)

            {

                log1.WriteEntry(ex.ToString(),EventLogEntryType.Error);

            }

            finally

            {

                excel.Quit();

            }

        }