1、DataGridView导出excel
//导出到xls;
//参数：dgvSource：为需要到处xls的dataGridView；strWordsheetName：为欲导出的表名;
//使用时不需要进行dgvSource为空的判断;
public void DgvToXls(DataGridView dgvSource,string strWorksheetName)
{
int intCount_blank=0;
//当dataGridView可以增加行时，空白dataGridView最小行为1;
if (dgvSource.AllowUserToAddRows)
intCount_blank = 1;
else
intCount_blank = 0;
//if (dgvSource.Rows.Count > intCount_blank)
//{
int intCount = 0;
Excel.Application myExcel=new Excel.Application(); ; //新建excel对象;
myExcel.Visible = true;
Excel._Workbook myWorkbook; //新建工作簿;
myWorkbook = (Excel._Workbook)(myExcel.Workbooks.Add(Missing.Value));
Excel._Worksheet myWorksheet; //新建工作表;
myWorksheet = (Excel._Worksheet)myWorkbook.ActiveSheet;//已经有了默认的sheet1表,可以进行操作;
myWorksheet = (Excel.Worksheet)myWorkbook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); //再增加一个新表;
myWorksheet.Name = strWorksheetName; //为新表赋值;
Excel._Worksheet WantDeleteSheet; //新建欲删除的工作表;
WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet1"];
WantDeleteSheet.Delete();
//WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet2"];
//WantDeleteSheet.Delete();
//WantDeleteSheet = (Excel._Worksheet)myWorkbook.Sheets["Sheet3"];
//WantDeleteSheet.Delete();
WantDeleteSheet = null;
try
{
//为excle表头赋值;
if (dgvSource.AllowUserToAddRows)
{
for (int k = 0; k < dgvSource.ColumnCount; k++)
{
myWorksheet.Cells[1, k + 1] = dgvSource.Columns[k].HeaderText.ToString();
}
for (int i = 0; i < dgvSource.Rows.Count; i++) //循环dataGridView;
{
for (int j = 0; j < dgvSource.ColumnCount; j++)
{
myWorksheet.Cells[i + 2, j + 1] = dgvSource.Rows[i].Cells[j].Value.ToString().Trim(); //写入工作表;
}
intCount++;
}
}
else
{
for (int k = 0; k < dgvSource.ColumnCount; k++)
{
myWorksheet.Cells[1, k + 1] = dgvSource.Columns[k].HeaderText.ToString();
}
for (int i = 0; i < dgvSource.Rows.Count; i++) //循环dataGridView;
{
for (int j = 0; j < dgvSource.ColumnCount; j++)
{
myWorksheet.Cells[i + 2, j + 1] = dgvSource.Rows[i].Cells[j].Value.ToString().Trim(); //写入工作表;
}
intCount++;
}
}
//列宽自适应;
myWorksheet.Columns.EntireColumn.AutoFit();
}
catch
{
MessageBox.Show("导出xls数据表时出现了错误，请检查！");
}
myWorksheet = null;
myWorkbook = null;
myExcel = null;
MessageBox.Show("导出完毕！共导出记录" + intCount.ToString() + "条。", "提示！", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
//else
//{
// MessageBox.Show("没有可以导出excle的内容,请先进行查询!");
//}
}
引用：DgvToXls(DataGridView1, “导出的数据”);
2、从xls中读取表到datatable
//从xls中读取表,并保证xls中没有空值(允许最后一列的值为空),否则返回null;
//参数：PathFileName：等于导入对话框的.FileName,strszLieming：是被导入电子表格的列名（必须有列名而不能直接就是数据）;
//使用时必须加上if (dtb != null),以判断是否读出的结果为空;在行循环内还必须加上第一个单元格是否为空的判断，已去除前六行系统数据;
//注意被导入的xls文件只能有一个表,支持中文;
public DataTable ReadFromXLS(string PathFileName,string[] strszLieming)
{
string strSql = "";
Boolean blCellIsEmpty = false;
int k = 0;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + PathFileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
//HDR=1;表示第一行为表头;
//IMEX=1;表示采用文本格式，但是如果前8行不出现文本格式的内容，就默认为字符串格式;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable tbFromXls = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//DataTable tbFromXls = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
try
{
strSql = "select ";
for (int i = 0; i < strszLieming.Length; i++)
{
if (i == strszLieming.Length - 1)
{
strSql = strSql + strszLieming[i];
}
else
{
strSql = strSql + strszLieming[i] + ",";
}
}
//if (tbFromXls.Rows.Count > 1)
//{
// MessageBox.Show("被导入xls文件中的表大于1个，表名为:" + tbFromXls.Rows[1]["Table_Name"].ToString() + "，请删除多余的表！或者尝试把内容复制到新的excle文件，并保存为03的xls格式！");
// tbFromXls = null;
// conn.Close();
// return null;
//}
OleDbCommand myOleDbCommand = new OleDbCommand(strSql + " FROM [" + tbFromXls.Rows[0]["Table_Name"].ToString() + "]", conn);
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
myData.Fill(tbFromXls);
conn.Close();
if (tbFromXls.Columns.Count == 10) //如果是空表,就是10列,和要导入表的格式和列数没有关系;
{
return null;
}
else
{
//判断是否有空值;
for (int j = 0; j < tbFromXls.Rows.Count; j++) //遍历整个表;
{
blCellIsEmpty = true;
//遍历第j行的每个单元格;
for (int m = 0; m < strszLieming.Length - strszLieming.Length + 1; m++) //为strszLieming.Length-1是因为最后一列不判断是否为空，因为一般都为备注;改为只判断第一列;
{
if (tbFromXls.Rows[j][strszLieming[m]].ToString() != string.Empty)
{
blCellIsEmpty = false;
}
//MessageBox.Show("第" + j.ToString() + "行的第一列：" + tbFromXls.Rows[j][strszLieming[m]].ToString());
}
if (blCellIsEmpty)
{
k = k + 1;
}
}
if (k > 300)
{
MessageBox.Show("被导入的数据都必须有值，但是有的为空白，因此所有数据都没有被处理，请检查！");
tbFromXls = null;
conn.Close();
return null;
}
else
{
return tbFromXls;
}
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
MessageBox.Show("请检查表的标题是否正确！", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
3、从excel转换datatable，使用Microsoft.Office.Interop.Excel
/// <summary>
/// 读取excel到datatable
/// </summary>
/// <param name="filePath"></param>
/// <param name="hasTitle"></param>
/// <returns></returns>
public static DataTable excelToDataTable(string filePath, bool hasTitle = false)
{
int iRowCount;
int iColCount;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
DataTable dt = new DataTable();
try
{
if (app == null) return null;
workbook = app.Workbooks.Open(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Microsoft.Office.Interop.Excel.Worksheet worksheet = sheets.get_Item(1);
if (worksheet == null) return null;
iRowCount = worksheet.UsedRange.Rows.Count;
iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
catch { return null; }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
扫一扫
201
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
