C#操作Excel

最新推荐文章于 2023-04-26 14:40:44 发布

weixin_30670925

最新推荐文章于 2023-04-26 14:40:44 发布

阅读量133

点赞数

文章标签： runtime c# 数据库

原文链接：http://www.cnblogs.com/tsyblog/p/3596692.html

版权

/// <summary>
/// 写入数据到 WPS Excel 中
/// </summary>
public void WriteToWpsExcel()
{
// 创建 Excel 对象
ET.Application excel = new WpsExcel.Application();

// 设置创建WorkBook时 WorkBook 包含的WorkSheet 的个数
excel.SheetsInNewWorkbook = 5;

// 创建 WorkBook(工作薄)
excel.Workbooks.Add();

// 获取第一个工作表
ET.Worksheet ws = excel.ActiveWorkbook.Worksheets[1];

// 给工作表设置名称
ws.Name = "测试4";

// 设置 Sheet 单元格的值
ws.Cells[1, 1] = "自动\t填充\n测试";

// 显示 Excel
excel.Visible = true;

// 线程休眠 2000毫秒
System.Threading.Thread.Sleep(2000);

// 保存当前活动的 WorkBook
excel.ActiveWorkbook.SaveAs(Environment.CurrentDirectory + "\\test4.xls",ET.XlFileFormat.xlExcel12);

// 关闭当前活动的 WorkBook
excel.ActiveWorkbook.Close();

// 退出 Excel 应用程序
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}

/// <summary>
/// 操作 Excel
/// </summary>
public class ExcelHandle
{
/// <summary>
/// 把 DataGridView中的学员信息写入到Excel中
/// </summary>
/// <param name="dgvStuInfo"></param>
public static void GetStudentInfoDataToWpsExcel2(DataGridView dgvStuInfo)
{
// 创建 Excel 对象
ET.Application excel = new ET.Application();
// 设置创建WorkBook时，WorkBook 包含的 WorkSheet 的个数
excel.SheetsInNewWorkbook = 1;
// 创建 WorkBook(工作薄)
excel.Workbooks.Add();

int col = 0; // 表示当前列Index
// 设置 Excel 列名
for (col = 0; col < dgvStuInfo.Columns.Count; col++)
{
excel.Cells[1, col + 1] = dgvStuInfo.Columns[col].HeaderText;
}

/*******************************
* 获取标题行的单元格，即Range *
*******************************/

// 方式一：需设置引用的dll的嵌入互操作类型为true
//ET.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, col]);

// 方式二：
ET.Range range = excel.Range[excel.Cells[1, 1], excel.Cells[1, col]];

// 设置字体加粗
range.Font.Bold = true;
// 设置字体颜色
range.Font.ColorIndex = 0;
// 设置背景颜色
range.Interior.ColorIndex = 15;
// 设置边框样式
range.Borders.LineStyle = ET.ETLineStyle.etContinuous;

// 循环将DataGridView 中的数据复制到Excel中
int i = 0, j = 0; // i 表示当前行，j表示当前列
for (i = 0; i < dgvStuInfo.Rows.Count; i++)
{
for (j = 0; j < dgvStuInfo.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = dgvStuInfo.Rows[i].Cells[j].Value.ToString();
}
}
/* 注：Range 表示一个范围：一个单元格到另一个单元格这之间的范围 */
// 设置出生年月日格式
// excel.Range[excel.Cells[2,8],excel.Cells[i+2,8]].NumberFormat="yyyy-m-d";

// 设置身份证号的格式
// excel.Range[excel.Cells[2, 10], excel.Cells[i + 2, 10]].NumberFormatLocal = "0";

// 设置电话号码的格式
excel.Range[excel.Cells[2, 5], excel.Cells[i + 2, 5]].NumberFormatLocal = "0";

// 显示当前窗口
excel.Visible = true;

// 系统休眠 3000毫秒(ms)
System.Threading.Thread.Sleep(3000);

// 保存当前活动的 WorkBook(工作薄)
excel.ActiveWorkbook.SaveAs(Environment.CurrentDirectory + "/MyExcel.xls", ET.XlFileFormat.xlExcel12);

// 关闭当前活动的 WorkBook(工作薄)
excel.ActiveWorkbook.Close();

// 退出 Excel 应用程序
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}

/// <summary>
/// 把 Excel 中的数据读取到 DataTable 中
/// </summary>
/// <param name="filepath">Excel文件的路径</param>
/// <param name="sheetName">需要读取的 Worksheet 名称</param>
/// <returns>DataTable(数据表)</returns>
public static System.Data.DataTable WriteDataToDataTableByExcel(string filepath, string sheetName)
{
// 保存读取到的数据
System.Data.DataTable dt = new System.Data.DataTable();

// 创建 Excel 对象
ET.Application excel = new ET.Application();
// 打开 Excel 文件
excel.Workbooks.Open(filepath);
// 获取需要导入的Excel Sheet
ET.Worksheet sheet = null;

// 循环遍历活动的 Workbook 中的所有 Worksheet
foreach (ET.Worksheet wsheet in excel.ActiveWorkbook.Worksheets)
{
// 判断要导出的 Worksheet表
if (sheetName == wsheet.Name)
{
sheet = wsheet;
break;
}
}
// 如果没有找到指定的 Worksheet
if (null == sheet)
return dt; // 则退出

// 保存当前列
int col = 0;
//　循环读取列名
while (true)
{
col++;
// 获取相应列的列名
string colName = (sheet.Cells[1,col] as ET.Range).Text.Trim();
// 如果当前列名为空，表示列名读取完毕，则结束循环
if (string.IsNullOrEmpty(colName))
break;
// 向 DataTable 中添加列
dt.Columns.Add(colName);
}

// 保存每行中空列的数量，如果空列数等于列数，则说明行数据已经读取完毕
int nullColumnCountOfRow = 1;
// true：表示数据读取完毕，否则反之
bool flag = false;
for (int j = 2; ; j++)
{
// 初始化每行空列数量
nullColumnCountOfRow = 0;
// 创建行（DataRow)
System.Data.DataRow dr = dt.NewRow();
// 循环读取每行中每列的值
for (int i = 1; i < col; i++)
{
// 获取当前列的值
string colText = (sheet.Cells[j, i] as ET.Range).Text.Trim();
// 如果当前列的值为空，则表示数据读取完毕
if (string.IsNullOrEmpty(colText))
{
// 判断空列数是否等于总列数
if (++nullColumnCountOfRow == col - 1)
{
flag = true; // true:表示数据读取完毕
break;
}
}
// 设置 (行)DataRow 中每列的值
dr[i - 1] = colText;
}
if (flag)
break;
// 向 DataTable 中添加行
dt.Rows.Add(dr);
}
// 退出 Excel 应用程序
excel_test.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_test);
excel_test = null;

// 返回 DataTable(数据表)
return dt;
}

/// <summary>
/// 导出 Excel 数据到 DataTable (OLEDB方式)
/// </summary>
/// <param name="filepath">Excel文件路径</param>
/// <param name="sheetName">Worksheet名称</param>
/// <returns>DataTable(数据表)</returns>
public static DataTable ExportExcelToDataTable(string filepath, string sheetName)
{
DataTable dt = new DataTable();

// 连接字符串 Provider:供应商 Data Source:Excel文件路径 Extended Properties:Excel的版本信息,
// HDR:表示是否把第一行作为列名，若为YES,表示为列名; IMEX=1:把混合型作为文本型读取。
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source="
+filepath+";"
+"Extended Properties='Excel 8.0; HDR=YES,IMEX=1'";

// 相当于 sql 语句，注意：表名（worksheet)后需加上$
//string strExcel = string.Format("select * from [{0}$] where 姓名"
// + " is not null", sheetName);
string strExcel = string.Format("select * from [{0}$]", sheetName);

using (OleDbConnection conn = new OleDbConnection(conStr))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
adapter.Fill(dt);

}
foreach(DataRow dr in dt.Rows)
{
dr[2] = dr[2].ToString() == "男" ? "1" : "0";
}

return dt;
}

/// <summary>
/// 把 DataTable 中的数据添加到数据库中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static int AddDataToDatabaseByDataTable(DataTable dt)
{
int addRowCount = 0;

string conStr = "Data source=.; Initial Catalog=MySchool; Integrated Security=true";
using (SqlConnection conn = new SqlConnection(conStr))
{
foreach (DataRow dr in dt.Rows)
{
int gradeId = -1;
if (null != dr[3])
{
string getGradeIdSql = string.Format("select gradeId from Grade where gradeName='{0}'", dr[3]);
gradeId = Convert.ToInt32(DBHelperSQL.ExecuteScalar(getGradeIdSql));
}
// 创建 sql 语句
StringBuilder sb = new StringBuilder();
sb.Append(" insert into [Student]");
sb.Append(" values");
sb.Append(" (@stuName,@pwd,@sex,@gradeId,@phone)");

SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@stuName",dr[0]),
new SqlParameter("@pwd",dr[1]),
new SqlParameter("@sex",dr[2]),
new SqlParameter("@gradeId",gradeId),
new SqlParameter("@phone",dr[4]),
};

SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.Parameters.AddRange(paras);
if (cmd.ExecuteNonQuery() > 0)
{
addRowCount++; // 添加成功的行数
}
}
}
// 返回成功导入的行数
return addRowCount;
// 返回是否全部成功导入
//return addRowCount==dt.Rows.Count?true:false;
}

/// <summary>
/// 打印九九乘法表到 Excel
/// </summary>
public static void PrintMultiplicationTableToExcel()
{
ET.Application excel = new ET.Application();
excel.SheetsInNewWorkbook = 3;
excel.Workbooks.Add();

ET.Worksheet wsheet = excel.ActiveWorkbook.Sheets[1];

ET.Range range = excel.Range[wsheet.Cells[1, 1], wsheet.Cells[1, 10]];
range.Merge(true); // 合并单元格
range.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
range.Font.Size = 16;
range.Font.Bold = true;
wsheet.Cells[1, 1] = "九九乘法表";
for (int i = 2; i <= 10; i++)
{
wsheet.Cells[2, i] = i-1;
}
for (int i = 3; i <= 9; i++)
{
wsheet.Cells[i, 1] = i-2;
for (int j = 1; j <= i; j++)
{
wsheet.Cells[i, j+1] = i + "*" + j + "=" + i * j;
}
}
excel.Visible = true;
System.Threading.Thread.Sleep(3000);

excel.ActiveWorkbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}

}

C# 操作 Excel 设置单元格格式：

range.NumberFormatLocal = "@"; //设置单元格格式为文本

range = (Range)worksheet.get_Range("A1", "E1"); //获取Excel多个单元格区域：本例做为Excel表头

range.Merge(0); //单元格合并动作

worksheet.Cells[1, 1] = "Excel单元格赋值"; //Excel单元格赋值

range.Font.Size = 15; //设置字体大小

range.Font.Underline=true; //设置字体是否有下划线

range.Font.Name="黑体"; 设置字体的种类

range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式

range.ColumnWidth=15; //设置单元格的宽度

range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb(); //设置单元格的背景色

range.Borders.LineStyle=1; //设置单元格边框的粗细

range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb()); //给单元格加边框

range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //设置单元格上边框为无边框

range.EntireColumn.AutoFit(); //自动调整列宽

Range.HorizontalAlignment= xlCenter; // 文本水平居中方式

Range.VerticalAlignment= xlCenter //文本垂直居中方式

Range.WrapText=true; //文本自动换行

Range.Interior.ColorIndex=39; //填充颜色为淡紫色

Range.Font.Color=clBlue; //字体颜色

xlsApp.DisplayAlerts=false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存

转载于:https://www.cnblogs.com/tsyblog/p/3596692.html