将C#的内容输出到Excel
如何将c#中dataGridView的内容输出到Excel文档中 --------------------编程问答--------------------/// <summary>--------------------编程问答-------------------- /// <summary>
/// 将DataGridVew 中数据放入Excel文件中
/// </summary>
/// <param name="dt">dataGridView控件名称</param>
/// <param name="outputPath">路径</param>
/// <param name="deleteOldFile">默认 true</param>
/// <param name="SheetName">工作薄名称</param>
public void DataSetToLocalExcelSort(DataGridView dt, string outputPath, bool deleteOldFile, string SheetName)
{
try
{
if (deleteOldFile)
{
if (System.IO.File.Exists(outputPath))
{
System.IO.FileInfo file = new System.IO.FileInfo(outputPath);
file.IsReadOnly = false;
System.IO.File.Delete(outputPath);
}
}
// Create the Excel Application object
Interop.Excel.Application excelApp = new Interop.Excel.Application();
// Create a new Excel Workbook
Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int a = excelApp.Hwnd;
int sheetIndex = 0;
int colColumns = 0, colRows = 0;
// Copy each DataTable
if (dt != null)
{
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
if (dt.Columns[col].Visible != false)
{
rawData[0, colColumns] = dt.Columns[col].HeaderText;
colColumns++;
}
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
if (dt.Columns[col].Visible != false)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, colRows] = dt.Rows[row].Cells[col].Value;
}
colRows++;
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Interop.Excel.Worksheet excelSheet = (Interop.Excel.Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, Type.Missing);
excelSheet.Name = SheetName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
//for (int i = 0; i < excelSheet.Columns.Count; i++)
//{
excelSheet.Columns.ColumnWidth = 13;
//}
// Mark the first row as BOLD
//((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
//excelApp.Application.AlertBeforeOverwriting = false;
excelApp.Application.DisplayAlerts = false;
// Save and Close the Workbook
excelWorkbook.SaveAs(outputPath, Missing.Value, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
IntPtr t = new IntPtr(excelApp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process ps = System.Diagnostics.Process.GetProcessById(k);
ps.Kill();
}
catch (Exception ex)
{
DataClass.UserFunc.WriteLog("保存Excel失败！" + ex.Message);
MessageBox.Show("保存失败！");
}
//killExcelProcess();
}
/// 将DataTable数据写入Excel文件（不分页）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="top">表格数据起始行索引</param>
/// <param name="left">表格数据起始列索引</param>
public void DataTableToExcel(System.Data.DataTable dt, int top, int left)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
//利用二维数组批量写入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
arr[j, k] = dt.Rows[j][k].ToString();
}
}
range = (Range)workSheet.Cells[top, left];
range = range.get_Resize(rowCount, colCount);
range.set_Value(missing, arr);
} --------------------编程问答--------------------
public void SaveAsExcel()--------------------编程问答-------------------- 楼上的是调用office的com组件，不要那么做，效率很低，利用二维数组才能解决效率低的问题，你可以测试100条数据看看就知道了 --------------------编程问答-------------------- 直接用System.oledb
{
//请采用以下调用方法,否则容易出现Excel进程不会自动退出
string path = "?????";
SaveAsExcel(path);
GC.Collect();
}
private void SaveAsExcel(string path)
{
object objMissing = System.Reflection.Missing.Value;
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook workBook = app.Workbooks.Add(objMissing);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
for (int i = 0; i < this.m_GridView.Columns.Count; i++)
{
Excel.Range head = (Excel.Range)workSheet.Cells[3, i + 1];
head.Value = this.m_GridView.Columns[i].HeaderText;
head = null;
}
Excel.Range range = workSheet.get_Range("A4", objMissing);
int rows = this.m_GridView.Rows.Count, cols = this.m_GridView.Columns.Count;
Excel.Range ranges = range.get_Resize(rows, cols);
object[,] datas = new object[rows, cols];
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
DataGridViewCell cell = this.m_GridView[j, i];
if (typeof(string) == cell.ValueType)
{
datas[i, j] = string.Format("'{0}", cell.FormattedValue);
}
else
{
datas[i, j] = cell.FormattedValue.ToString();
}
}
}
ranges.Value = datas;
workBook.SaveAs(path, objMissing, objMissing, objMissing, objMissing, objMissing, Excel.XlSaveAsAccessMode.xlNoChange, objMissing,
objMissing, objMissing, objMissing);
workBook.Close(false, objMissing, objMissing);
app.Quit();
range = null;
ranges = null;
workBook = null;
workSheet = null;
app = null;
}
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()
cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=C:\myData.XLS;Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
上面是select 数据, 导入的话用 insert 语句
补充：.NET技术 , C#
