private static string Path = @"D:\我的文档\Sheet1.xlsx";//需要转换的原始数据路径 private static string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;"; private void Change() { try { string strSql3 = "select 共几件, a.订单编号,客户名称,第几件 from [订单数据$] a left join " + "(select count(订单编号) as 共几件,订单编号 from (select 订单编号,客户名称,第几件 from [订单数据$] " + "where 画面编号<>'合计' group by 订单编号,客户名称,第几件) b group by 订单编号) c " + " on a.订单编号=c.订单编号 where 画面编号<>'合计' group by a.订单编号,客户名称,第几件,共几件"; System.Data.DataTable dt3 = new System.Data.DataTable(); try { if (!File.Exists(Path)) { return; } OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //每笔订单编号的总数 OleDbDataAdapter oda3 = new OleDbDataAdapter(strSql3, strConn);
private static string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;"; private void Change()
{
try
{
string strSql3 = "select 共几件, a.订单编号,客户名称,第几件 from [订单数据$] a left join " +
"(select count(订单编号) as 共几件,订单编号 from (select 订单编号,客户名称,第几件 from [订单数据$] " +
"where 画面编号<>'合计' group by 订单编号,客户名称,第几件) b group by 订单编号) c " +
" on a.订单编号=c.订单编号 where 画面编号<>'合计' group by a.订单编号,客户名称,第几件,共几件";
System.Data.DataTable dt3 = new System.Data.DataTable();
try
{
if (!File.Exists(Path))
{
return;
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open(); //每笔订单编号的总数
OleDbDataAdapter oda3 = new OleDbDataAdapter(strSql3, strConn);
oda3.Fill(dt3); conn.Close();
conn.Dispose();
}
catch(Exception ex)
{
return;
//文件不存在,10分钟之后继续运行
} string[] firstFile = new string[dt3.Rows.Count];
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(); //获得转换后的文件名
firstFile[i] = savePath;
}
//移动已转换的原始数据文件
FileInfo file = new FileInfo(Path);
if (file.Exists)
{
string movePath = @"D:\我的文档\Sheet1.xlsx";
//file.MoveTo(movePath);
string movePath2 = @"D:\我的文档\Data\"+DateTime.Now.ToString("yyyy年MM月dd日HH点mm分ss秒")+"Sheet1.xlsx";
System.IO.Directory.Move(movePath, movePath2);
} Excel.Application delExcel = null;
//循环打开文件
for (int i = 0; i < firstFile.Length;i++ )
{
delExcel = new Excel.Application();
delExcel.Application.Workbooks.Add(firstFile[i]);
delExcel.Visible = true;
System.Threading.Thread.Sleep(1500);//暂停1.5秒再继续，供用户打印
//杀死Excel进程
Process[] ProExcel = Process.GetProcessesByName("EXCEL");
for (int k = 0; k < ProExcel.Length; k++)
{
if (ProExcel[k].MainWindowHandle.ToInt32() == 0)
{
try
{
ProExcel[k].Kill();
}
catch (Exception)
{ }
}
}
for (int z = 0; z < 20000;z++ )//循环20000次删除
{
try
{
//删除文件
System.IO.File.Delete(firstFile[i]);
break;//如果删除成功就跳出循环
}
catch(Exception e)
{
//否则出现异常，暂停1.5秒继续删除
System.Threading.Thread.Sleep(1500);
//杀死Excel进程
Process[] ProExcel2 = Process.GetProcessesByName("EXCEL");
for (int k = 0; k < ProExcel2.Length; k++)
{
if (ProExcel2[k].MainWindowHandle.ToInt32() == 0)
{
try
{
ProExcel2[k].Kill();
}
catch (Exception)
{ }
}
}
continue;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("出现异常："+ex.Message);
}
{//导出到ＥXCEL重载,含一些汇总信息 try
{
int i, j; //创建一个Excel文件 Excel.Application myExcel = new Excel.Application();
myExcel.Application.Workbooks.Add(true); //让Excel文件可见 myExcel.Visible = true; //第一行为报表名称 myExcel.Cells[1, 1] = TableName;
//插入列标题
for (i = 1; i <= ds.Tables[0].Columns.Count; i++)
myExcel.Cells[2, i] = ds.Tables[0].Columns[i - 1].Caption; //将内容导入excel
for (i = 1; i <= ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= ds.Tables[0].Columns.Count; j++)
{
myExcel.Cells[2 + i, j] = ds.Tables[0].Rows[i - 1][j - 1];
}
} //将string 数组的信息在表的下部显示出来
for (i = 0; i < strs.Length; i++)
myExcel.Cells[4 + ds.Tables[0].Rows.Count + i, 1] = strs[i];
myExcel.Application.DisplayAlerts = false;
}
catch (Exception ex)
{
ex.Message.ToString();
} }
那好像上面的几位写的只是操作文档内容。而不是可以在自己的软件里面机型编辑。
记录一下，学习。