111,129
社区成员
发帖
与我相关
我的任务
分享C#导出excel
public static string exprotExcel(string title, DataGridView exprotGrid)
{
string message = "";
if (exprotGrid.Rows.Count == 0)
{
message = "当前数据为空，不执行导出";
return message;
}
Microsoft.Office.Interop.Excel.Application exlapp = new Microsoft.Office.Interop.Excel.Application();
if (exlapp == null)
{
message = "excel无法启动";
return message;
}
Microsoft.Office.Interop.Excel.Workbook exlbook = exlapp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet exlsheet = (Microsoft.Office.Interop.Excel.Worksheet)exlbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = exlsheet.get_Range(exlapp.Cells[1, 1], exlapp.Cells[1, exprotGrid.ColumnCount]);
int colIndex = 0;
int rowIndex = 0;
int colCount = exprotGrid.ColumnCount;
int rowCount = exprotGrid.RowCount;
object[,] objdata = new object[rowCount + 1, colCount];
for (colIndex = 0; colIndex < colCount; colIndex++)
{
objdata[rowIndex, colIndex] = exprotGrid.Columns[colIndex].HeaderText;
}
for (rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
for (colIndex = 0; colIndex < colCount; colIndex++)
{
if (exprotGrid[colIndex, rowIndex].Value != null)
{
objdata[rowIndex + 1, colIndex] = exprotGrid[colIndex, rowIndex].Value.ToString();
}
else
{
objdata[rowIndex + 1, colIndex] = "";
}
}
Application.DoEvents();
}
range = exlsheet.get_Range(exlapp.Cells[2, 1], exlapp.Cells[rowCount + 2, colCount]);
range.Value2 = objdata;
try
{
exlapp.Cells.EntireColumn.AutoFit();
exlapp.Visible = true;
}
catch
{
message = "保存出错，请检查";
return message;
}
return message;
}
以上的代码是导出excel，弹出excel界面，但是导出的数据总是第一行是空的，好像默认第一行是表头的，我怎么才能把第一行去掉呢？还有怎么用select * from [sheet1$]选出 excel中的非空行数据呢 我现在不知道列名 ，怎么写 第一列！=“”？
...全文
请发表友善的回复…
发表回复
honglangzhu 2010-10-28
- 打赏
- 举报
自己关注下。。。
xiemeilin 2010-10-28
- 打赏
- 举报
range = exlsheet.get_Range(exlapp.Cells[2, 1], exlapp.Cells[rowCount + 2, colCount]);
饿的是你这个get_range有问题，不如改用“A1”之类的来比如 range = exlsheet.get_Range(“A1”,Missing.Value);
饿的是你这个get_range有问题，不如改用“A1”之类的来比如 range = exlsheet.get_Range(“A1”,Missing.Value);
victyhappy 2010-10-28
- 打赏
- 举报
我也想知道你这个问题的答案...
金木水火土成就万物 2010-10-27
- 打赏
- 举报
关注中……
honglangzhu 2010-10-27
- 打赏
- 举报
问文件流 保存到excel出现这个问题就那么难吗，真没人知道吗，我现在这个文件导出后 再用程序打开会出错了，用excel打开说格式不正确
victyhappy 2010-10-26
- 打赏
- 举报
[Quote=引用 7 楼 honglangzhu 的回复:]
没人知道么？用流的bug?导出后保存的文件打开总是说格式与文件扩展名格式不一样
[/Quote]
关注.....
没人知道么？用流的bug?导出后保存的文件打开总是说格式与文件扩展名格式不一样
[/Quote]
关注.....
honglangzhu 2010-10-26
- 打赏
- 举报
没人知道么？用流的bug?导出后保存的文件打开总是说格式与文件扩展名格式不一样
honglangzhu 2010-10-26
- 打赏
- 举报
用流保存的文件为什么打开的时候总是跳出来格式与文件扩展名格式不一样
laomengzi 2010-10-25
- 打赏
- 举报
[Quote=引用 4 楼 wuyq11 的回复:]
http://topic.csdn.net/u/20100312/14/4159d5ec-43ad-4f41-bc73-9a97421eadc6.html
[/Quote]
用流保存成xls文件. 这种方法比较好,不用引用Excel组件
http://topic.csdn.net/u/20100312/14/4159d5ec-43ad-4f41-bc73-9a97421eadc6.html
[/Quote]
用流保存成xls文件. 这种方法比较好,不用引用Excel组件
zhao_zps 2010-10-25
- 打赏
- 举报
/// <summary>
/// 把DataGridView中的数据 导出到EXCEL /// </summary>
/// <param name="dg"></param>
public static void input_excel(DataGridView dg)
{
Excel._Worksheet Sht;
Excel._Workbook Bo;
Excel.Application excel = new Excel.Application();
Bo = excel.Application.Workbooks.Add(true);
// excel.Visible = true;//excel是否显示
Sht = (Excel.Worksheet)Bo.Sheets[1];
//写入数据到EXCEL
int Rowed = 0;
if (dg.AllowUserToAddRows == true)
{
for (int i = 0; i < dg.Rows.Count - 1; i++)
{
for (int y = 1; y <= dg.ColumnCount; y++)
{
excel.Cells[1, y] = dg.Columns[y - 1].HeaderText;
}
Rowed++;
if (Rowed < 65000)
{
for (int lie = 0; lie < dg.ColumnCount; lie++)
{
excel.Cells[Rowed + 1, lie + 1] = Convert.ToString(dg[lie, i ].Value);
}
}
else
{
Sht = (Excel.Worksheet)Bo.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Rowed = 0;
i--;
}
}
}
else
{
for (int i = 0; i < dg.Rows.Count - 1; i++)
{
for (int y = 1; y <= dg.ColumnCount; y++)
{
excel.Cells[1, y] = dg.Columns[y - 1].HeaderText;
}
Rowed++;
if (Rowed < 65000)
{
for (int lie = 0; lie < dg.ColumnCount; lie++)
{
excel.Cells[Rowed + 1, lie + 1] = Convert.ToString(dg[lie, i].Value);
}
}
else
{
Sht = (Excel.Worksheet)Bo.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Rowed = 0;
i--;
}
}
}
excel.Visible = true;
}
zhao_zps 2010-10-25
- 打赏
- 举报
/// <summary>
/// 把DataGridView中的数据 导出到EXCEL /// </summary>
/// <param name="dg"></param>
public static void input_excel(DataGridView dg)
{
Excel._Worksheet Sht;
Excel._Workbook Bo;
Excel.Application excel = new Excel.Application();
Bo = excel.Application.Workbooks.Add(true);
// excel.Visible = true;
Sht = (Excel.Worksheet)Bo.Sheets[1];
//写入数据到EXCEL
int Rowed = 0;
if (dg.AllowUserToAddRows == true)
{
LB.ListB("共计 " +Convert.ToString ( dg.Rows.Count-1) + "条", Lb);
LB.ListB("", Lb);
for (int i = 0; i < dg.Rows.Count - 1; i++)
{
for (int y = 1; y <= dg.ColumnCount; y++)
{
excel.Cells[1, y] = dg.Columns[y - 1].HeaderText;
}
Rowed++;
if (Rowed < 65000)
{
for (int lie = 0; lie < dg.ColumnCount; lie++)
{
excel.Cells[Rowed + 1, lie + 1] = Convert.ToString(dg[lie, i ].Value);
}
}
else
{
Sht = (Excel.Worksheet)Bo.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Rowed = 0;
i--;
}
}
}
else
{
LB.ListB("共计 " + Convert.ToString(dg.Rows.Count ) + "条", Lb);
LB.ListB("", Lb);
for (int i = 0; i < dg.Rows.Count - 1; i++)
{
for (int y = 1; y <= dg.ColumnCount; y++)
{
excel.Cells[1, y] = dg.Columns[y - 1].HeaderText;
}
Rowed++;
if (Rowed < 65000)
{
for (int lie = 0; lie < dg.ColumnCount; lie++)
{
excel.Cells[Rowed + 1, lie + 1] = Convert.ToString(dg[lie, i].Value);
}
}
else
{
Sht = (Excel.Worksheet)Bo.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Rowed = 0;
i--;
}
}
}
excel.Visible = true;
}
poloyzhang 2010-10-25
- 打赏
- 举报
public static void allTable_toExcel(string outputFileName, System.Data.DataSet receivedDataSet, string receivedDataSetOfTableName)
{
try
{
if (receivedDataSet == null) return;
//string currentDate = DateTime.Now.ToShortDateString();
string currentDate = DateTime.Now.ToString();
string saveFileName = "";
currentDate = formatString(currentDate);
bool fileSaved = false;
System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog();
//saveDialog.DefaultExt = "xls";
saveDialog.DefaultExt = "csv";
//saveDialog.Filter = "Excel 文件|*.xls";
saveDialog.Filter = "csv 文件|*.csv|Excel 文件|*.xls";
saveDialog.FileName = outputFileName + "_" + currentDate;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
System.Windows.Forms.MessageBox.Show("无法创建Excel对象，可能您未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段
for (int i = 0; i < receivedDataSet.Tables[receivedDataSetOfTableName].Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = receivedDataSet.Tables[receivedDataSetOfTableName].Columns[i].ColumnName;
}
//写入数值
for (int r = 0; r < receivedDataSet.Tables[receivedDataSetOfTableName].Rows.Count; r++)
{
for (int i = 0; i < receivedDataSet.Tables[receivedDataSetOfTableName].Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = receivedDataSet.Tables[receivedDataSetOfTableName].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
//if (this.Text != "Notification")
{
Microsoft.Office.Interop.Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[receivedDataSet.Tables[0].Rows.Count + 1, 2]);
rg.NumberFormat = "00000000";
}
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
System.Windows.Forms.MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();// 强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEl
}
catch (Exception exc)
{
exc.ToString();
}
}
