C#WinForm怎样导出EXCEL，要求一定的格式
C#WinForm怎样导出EXCEL，我已经导出数据了，后面的格式我不知道怎么做。
比如数据从第几行开始导入；那位任兄知道，小弟在此谢谢了， 下面是我的代码：有不妥的地方请说一下，谢谢
using System.Data.OleDb;
//using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
namespace test0608
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string strcon = @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\db1.mdb";
OleDbConnection cn = new OleDbConnection(strcon);
OleDbDataAdapter da = new OleDbDataAdapter("select * from liu", cn);
DataSet ds = new DataSet();
da.Fill(ds, "liu");
System.Data.DataTable dt = ds.Tables["liu"];
ExportToExcel(dt);
System.Windows.Forms.Application.Exit();
}
private void ExportToExcel(DataTable dataTable)
{
SaveFileDialog _saveFileDialog = new SaveFileDialog();
_saveFileDialog.Filter = "Microsoft Excel (*xls)|*.xls";
_saveFileDialog.Title = "输出统计结果";
if (MessageBox.Show("需要保存文件吗？", "提示框", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (_saveFileDialog.ShowDialog() == DialogResult.OK)
{
Stream _stream = _saveFileDialog.OpenFile();
StreamWriter _streamWriter = new StreamWriter(_stream, Encoding.GetEncoding("GB2312"));
string _columnName = "";
int _columnCount = dataTable.Columns.Count;
for (int i = 0; i < _columnCount; i++)
{
if (i == 0)
{
dataTable.Columns[0].ColumnName = "id";
}
if (i > 0)
{
_columnName += "\t";
}
_columnName += dataTable.Columns[i].ColumnName;
}
_streamWriter.WriteLine(_columnName);
int _rowCount = dataTable.Rows.Count;
for (int i = 0; i < _rowCount; i++)
{
string _rowValue = "";
for (int j = 0; j < _columnCount; j++)
{
if (j > 0)
{
_rowValue += "\t";
}
_rowValue += dataTable.Rows[i][j].ToString();
}
_streamWriter.WriteLine(_rowValue);
}
_streamWriter.Close();
_stream.Close();
}
[解决办法]
你是要设置Excel的格式吗，你这样方式没法设置Excel的格式，要用Excel组件才行。
[解决办法]
不是有EXCEL组件吗？直接调用这个COM组件来实现啊！一行一行插入数据啊，该空几行就空几行！如果不懂如何用EXCEL组件，请百度搜索：C#二次开发EXCEL！
[解决办法]
Excel.Application appExcel = null;
for (int i = 0; i < dt3.Rows.Count; i++)
{
object eFileName = @"D:\我的文档\Templates.xlsx";//模板文件路径
appExcel = new Excel.Application();
appExcel.Visible = false;
appExcel.UserControl = true;
Workbooks workBooks = appExcel.Workbooks;
_Workbook workBook = workBooks.Add(eFileName);
Sheets sheets = workBook.Worksheets;
_Worksheet workSheet = (_Worksheet)sheets.get_Item(1);
//根据订单编号和第几件查询数据
string number = dt3.Rows[i]["订单编号"].ToString();
string count = dt3.Rows[i]["第几件"].ToString();
System.Data.DataTable dataTable = GetDataByNumber(number, count);
int amount = dataTable.Rows.Count;
int total = 0;
if (amount > 1)
{
for (int j = 0; j < amount; j++)
{
if (j > 0)//因已存在一列，故少插入一列
{
workSheet.get_Range(appExcel.Cells[11, 1], appExcel.Cells[11, 5]).EntireRow.Insert(Missing.Value, Missing.Value);
}
//合并单元格：规格
Excel.Range column = workSheet.get_Range("A11", "B11");
column.Merge(0);
appExcel.Cells[11, 1] = dataTable.Rows[j]["规格"].ToString();
appExcel.Cells[11, 3] = dataTable.Rows[j]["画面编号"].ToString();
appExcel.Cells[11, 4] = dataTable.Rows[j]["数量"].ToString();
total += Convert.ToInt32(dataTable.Rows[j]["数量"]);
}
int a = amount + 10;
//合并单元格：合计列
Excel.Range columns = workSheet.get_Range("E11", "E" + a + "");
columns.Merge(0);
appExcel.Cells[11, 5] = total;
}
else
{
appExcel.Cells[11, 1] = dataTable.Rows[0]["规格"].ToString();
appExcel.Cells[11, 3] = dataTable.Rows[0]["画面编号"].ToString();
appExcel.Cells[11, 4] = dataTable.Rows[0]["数量"].ToString();
appExcel.Cells[11, 5] = dataTable.Rows[0]["数量"].ToString();
}
//插入数据
appExcel.Cells[4, 1] = dt3.Rows[i]["客户名称"].ToString();//收货单位名称
appExcel.Cells[6, 1] = "订单号：" + dt3.Rows[i]["订单编号"].ToString();//订单编号
appExcel.Cells[6, 3] = "发货日期：" + DateTime.Now.ToString("yyyy.MM.dd");//发货日期
appExcel.Cells[8, 1] = "共" + dt3.Rows[i]["共几件"].ToString() + "件，第" + dt3.Rows[i]["第几件"].ToString() + "件。 内附清单：是□ 否□";//数量
Excel.Range allDataWithTitleRange = workSheet.get_Range(appExcel.Cells[4, 1], appExcel.Cells[4, 1]);
allDataWithTitleRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;//格式居中
//合计栏文本靠下
Excel.Range sum = workSheet.get_Range(appExcel.Cells[11, 5], appExcel.Cells[11, amount+10]);
sum.VerticalAlignment = XlVAlign.xlVAlignBottom;
//string date = DateTime.Now.ToString("yyyy年MM月dd日HH点mm分ss秒");
//int num = i + 1;
string FileName = dt3.Rows[i]["订单编号"].ToString() + "_" + dt3.Rows[i]["第几件"].ToString();
string savePath = @"D:\我的文档\Excel" + FileName + ".xlsx";//转换成功后的保存路径
workBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
appExcel.Quit();
GC.Collect();
===========================
这是根据模板导出Excel的部分代码
}
