winform程序如何将DataTable数据导入到Excel文件中,用文件流的形式可以吗？
能给个代码参考下么 谢谢了。winformExcel c#
能给个代码参考下么 谢谢了。winformExcel c#
解决方案 »
- 主项目如何向子项目传值
- 关于正则表达式
- 关于RDLC报表中的表达式问题
- c# 数据显示的问题
- comboBox控件的问题－－怎样让comboBox显示时有一个默认值？
- 剧难的问题，google上都搜不到
- ChangePassword 控件的使用方法？？？？？？？？？？？？？？？？？？？？急急急急急急急急急急急急急急急急急急急急急急急急
- 水晶报表是否可以实现报表的连续打印功能，也就是不分页打印?
- 没写完的俄罗斯(C#版),请高手写一下消层的算法.现在的代码已经可以运行了.但还有点问题.请高手帮忙找找原因.100分相赠!
- C#里面如何调出打印对话框？
- c#连MYSQL乱码！求救！！！
- c#winform访问多个链接的问题
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"table1");
return ds;
}
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
//cmd.ExecuteNonQuery ();
for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
{
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
}
用流可以的
http://blog.csdn.net/happy09li/article/details/7431967
public static bool ExportExcelWithXML(System.Data.DataTable dt, string path)
{
bool succeed = false;
if (dt == null)
{
// 导出为XML格式的Excel文件，需要事先准备好XML格式的Excel文件作为模版
try
{
XmlDocument doc = new XmlDocument();
doc.Load(System.Windows.Forms.Application.StartupPath + @"\XLS\ExportXML.xls");
XmlNode root = doc.DocumentElement;
XmlNodeList xnlist = root.ChildNodes;
XmlElement sheet = null;
XmlElement documentPro = null;
XmlElement styles = null;
foreach (XmlNode xn in xnlist)
{
XmlElement xe = (XmlElement)xn;
if (xe.Name == "DocumentProperties")
{
documentPro = xe;
}
else if (xe.Name == "Worksheet")
{
sheet = xe;
}
else if (xe.Name == "Styles")
{
styles = xe;
}
} if (documentPro == null || sheet == null || styles == null)
{
return false;
} // 写入Sheet名
sheet.SetAttribute("Name", ssNameSpace, dt.TableName); // 添加Style
XmlElement styleColumnName = doc.CreateElement("Style", ssNameSpace);
styleColumnName.SetAttribute("ID", ssNameSpace, "s16");
XmlElement fontColumnName = doc.CreateElement("Font", ssNameSpace);
fontColumnName.SetAttribute("FontName", ssNameSpace, "Arial");
fontColumnName.SetAttribute("Family", xNameSpace, "Swiss");
fontColumnName.SetAttribute("Color", ssNameSpace, "#000000");
fontColumnName.SetAttribute("Bold", ssNameSpace, "1");
styleColumnName.AppendChild(fontColumnName);
styles.AppendChild(styleColumnName); XmlElement styleRow = doc.CreateElement("Style", ssNameSpace);
styleRow.SetAttribute("ID", ssNameSpace, "s17");
XmlElement fontRow = doc.CreateElement("Font", ssNameSpace);
fontRow.SetAttribute("FontName", ssNameSpace, "Arial");
fontRow.SetAttribute("Family", xNameSpace, "Swiss");
fontRow.SetAttribute("Color", ssNameSpace, "#000000");
styleRow.AppendChild(fontRow);
styles.AppendChild(styleRow); // 写入表格内容
XmlNode table = sheet.FirstChild; // 写入行列个数
((XmlElement)table).SetAttribute("ExpandedColumnCount", ssNameSpace, dt.Columns.Count.ToString());
((XmlElement)table).SetAttribute("ExpandedRowCount", ssNameSpace, (dt.Rows.Count + 2).ToString()); // 添加列宽
for (int i = 0; i < dt.Columns.Count; i++)
{
XmlElement column = doc.CreateElement("Column", ssNameSpace);
column.SetAttribute("Width", ssNameSpace, "100");
column.SetAttribute("AutoFitWidth", ssNameSpace, "1");
table.AppendChild(column);
} // 添加列名
XmlElement columnName = doc.CreateElement("Row", ssNameSpace);
for (int i = 0; i < dt.Columns.Count; i++)
{
XmlElement columnCell = doc.CreateElement("Cell", ssNameSpace);
columnCell.SetAttribute("StyleID", ssNameSpace, "s16"); XmlElement data = doc.CreateElement("ss:Data", ssNameSpace);
data.SetAttribute("Type", ssNameSpace, "String");
data.InnerText = dt.Columns[i].ToString(); columnCell.AppendChild(data);
columnName.AppendChild(columnCell);
}
table.AppendChild(columnName); // 添加行
for (int i = 0; i < dt.Rows.Count; i++)
{
XmlElement row = doc.CreateElement("Row", ssNameSpace);
for (int j = 0; j < dt.Columns.Count; j++)
{
XmlElement cell = doc.CreateElement("Cell", ssNameSpace);
cell.SetAttribute("StyleID", ssNameSpace, "s17"); XmlElement data = doc.CreateElement("Data", ssNameSpace);
data.SetAttribute("Type", ssNameSpace, "String");
data.InnerText = dt.Rows[i][j].ToString(); cell.AppendChild(data);
row.AppendChild(cell);
}
table.AppendChild(row);
} DateTime now = DateTime.Now;
string timeString = string.Format("{0}T{1}Z", now.ToShortDateString(), now.ToLongTimeString());
XmlNodeList docProNodeList = documentPro.ChildNodes;
foreach (XmlNode xn in docProNodeList)
{
if (xn.Name == "Author" || xn.Name == "LastAuthor")
{
// 写入作者和修改者
xn.InnerText = Environment.UserName;
}
else if (xn.Name == "Created" || xn.Name == "LastSaved")
{
// 写入创建时间和修改时间
xn.InnerText = timeString;
}
else if (xn.Name == "Company")
{
// 写入公司名
xn.InnerText = System.Windows.Forms.Application.CompanyName;
}
} doc.Save(path);
succeed = true;
}
catch (Exception e)
{
succeed = false;
}
} return succeed;
}
/// <summary>
/// 把DataTable导出到EXCEL不是科学计算法
/// </summary>
/// <param name="dt">DataTable数据源表</param>
/// <param name="saveFileName">EXCEL全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel_NoScientificCalc(System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
MessageBoxHepler.ShowErrorMsgBox("要到出的数据集为空!");
return false;
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
MessageBoxHepler.ShowErrorMsgBox("不能创建EXCEL文件,请检查您的机器是否正确安装过EXCEL!");
return false;
}
//xlApp.Visible = true; //打开EXCEL 可以清楚的看到导入过程,这样省掉了进度条的功能但是加长的导出时间 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];//取得第一个SHEET
worksheet.Name = dt.TableName;
worksheet.Cells.Font.Size = 10; //字体大小
Microsoft.Office.Interop.Excel.Range range; worksheet.get_Range("A1", "D1").Merge((worksheet.get_Range("A1", "D1").MergeCells));
worksheet.get_Range("E1", "G1").Merge((worksheet.get_Range("E1", "G1").MergeCells));
worksheet.Cells[1, 1] = txtConsignor.Text;
worksheet.Cells[1, 5] = ghdtc.DtpStartDate.ToString("yyyy-MM-dd") + " - " + ghdtc.DtpEndDate.ToString("yyyy-MM-dd");
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 34;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 5];
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Interior.ColorIndex = 34;
((Microsoft.Office.Interop.Excel.Range)worksheet.Rows["1:1", System.Type.Missing]).RowHeight = 22; //写入列头字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
if (i == 5)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i];
range.NumberFormatLocal = "@";
}
} //写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == "报关" || dt.Columns[i].ColumnName == "三检" || dt.Columns[i].ColumnName == "其他" || dt.Columns[i].ColumnName == "合计")
{
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1]).NumberFormatLocal = "$#,##0.00";//更改格式为文本格式
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
range = worksheet.get_Range("O"+(r+3).ToString(), System.Type.Missing);
range.Formula = "=SUM(L"+(r+3).ToString()+":N"+(r+3).ToString()+")";
range.Calculate(); range = worksheet.get_Range("L" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
range.Formula = "=SUM(L3:L" + (dt.Rows.Count - 2 + 3).ToString() + ")";
range.Calculate(); range = worksheet.get_Range("M" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
range.Formula = "=SUM(M3:M" + (dt.Rows.Count - 2 + 3).ToString() + ")";
range.Calculate(); range = worksheet.get_Range("N" + (dt.Rows.Count - 1 + 3).ToString(), System.Type.Missing);
range.Formula = "=SUM(N3:N" + (dt.Rows.Count - 2 + 3).ToString() + ")";
range.Calculate();
}
else
{
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 3, i + 1]).NumberFormatLocal = "@";//更改格式为文本格式
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
}
}
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
range.EntireColumn.AutoFit();
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);//保存文件
return true;
}
catch (Exception ex)
{
MessageBoxHepler.ShowErrorMsgBox("保存文件时出错,要保存的文件名错误或者文件可能正被打开!\r\n" + ex.Message);
return false;
}
finally
{
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
} // System.Diagnostics.Process excel = System.Diagnostics.Process.GetCurrentProcess(); xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
KillSpecialExcel(xlApp);
xlApp = null;
} GC.Collect();
}
}
用流可以的
http://blog.csdn.net/happy09li/article/details/7431967
+1我写的代码 就是 利用 你的
高手
用流可以的
http://blog.csdn.net/happy09li/article/details/7431967我想问一下“ for(int i=0;i<fp2.Sheets [0].RowCount -1;i++) ”
的“fp2”是在哪来的？找不到啊！
用流可以的
http://blog.csdn.net/happy09li/article/details/7431967我想问一下“ for(int i=0;i<fp2.Sheets [0].RowCount -1;i++) ”
的“fp2”是在哪来的？找不到啊！ Excel.Workbook book
{
try
{
object missingValue = System.Reflection.Missing.Value;
Application excel = new Application();
excel.Visible = false;
_Workbook workBook = excel.Workbooks.Add(missingValue);
int excelRow = 0;
int excelColumn = 0;
for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
{
excelRow = rowIndex + 1;
excelColumn = columnIndex + 1;
excel.Cells[excelRow, excelColumn] = dataTable.Rows[rowIndex][columnIndex];
}
}
workBook.SaveAs(@"D:\test.xls", missingValue, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
workBook.Close(false, missingValue, missingValue);
excel.Quit();
}
catch (Exception exception)
{
}
}