前两天一个long long ago的客户的助理MM通过MSN联系到我,楚楚可怜(用了很多MSN表情)的说有个很紧急的事,让我帮她个忙。原来他们公司年终盘点,有8k多种产品,每种产品要生成一个盘点单(excel sheet)。产品列表也在一个叫goods.xsl的excel表中。这位助理MM会点宏,可惜功底太弱,做不来。
我本就乐于助人,现又不禁怜香惜玉起来,正要满口答应的时候,mm又说:"老大说了,这会给报酬的哦!",我喜出望外,然后商定完成后给我500大圆。ok,我在网上Ctrl+c,Ctrl +v了一通,做成了下面这个值500大圆的代码。
class Program
{
static void Main(string[] args)
{
PrintFilledCard();
}
private static void PrintFilledCard()
{
Console.Write("开始号:");
string stmp = Console.ReadLine();
int start = int.Parse(stmp);
Console.Write("结束号:");
stmp = Console.ReadLine();
int end = int.Parse(stmp);
Console.WriteLine("Waitting...");
string cardFileName = @"D:\Code\ExcelAuto\Cards.xls";
string goodsFileName = @"D:\Code\ExcelAuto\Goods.xls";
string saveFileName = @"D:\Code\ExcelAuto\Cards1.xls";
object missing = System.Reflection.Missing.Value;
Excel.Application ThisApplication = new ApplicationClass();
Workbook workBookTemplate = null;
Workbook workBookGoods = null;
try
{
//加载产品文件
workBookGoods = ThisApplication.Workbooks.Open(goodsFileName, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Worksheet sheetGoods = (Worksheet)workBookGoods.Sheets[1];
//加载Excel模板文件
workBookTemplate = ThisApplication.Workbooks.Open(cardFileName, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Worksheet sheetTemplate = (Worksheet)workBookTemplate.Sheets[1];
ThisApplication.Visible = false;
int index = start + 1;
int errorCount = 0;
int totalCount = sheetGoods.UsedRange.Rows.Count - 1;
// int totalCount = (int)numericTotal.Value;
end = end + 1;
object goodsSN = null;
object goodsID = null;
object goodsName = null;
object goodsDepository = null;
object goodsUnit = null;
string pageFooter;
DateTime startTime = DateTime.Now;
while (index <= end && errorCount < 3)
{
sheetTemplate.Copy(missing, workBookTemplate.Sheets[workBookTemplate.Sheets.Count]);
Worksheet sheetNew = (Worksheet)workBookTemplate.Sheets[workBookTemplate.Sheets.Count];
sheetNew.Name = (index - 1).ToString();
try
{
goodsSN = sheetGoods.Cells[index, 1];
goodsID = sheetGoods.Cells[index, 2];
goodsName = sheetGoods.Cells[index, 3];
goodsUnit = sheetGoods.Cells[index, 4];
goodsDepository = sheetGoods.Cells[index, 5];
sheetNew.Cells[4, 2] = goodsSN;
sheetNew.Cells[5, 2] = goodsID;
sheetNew.Cells[6, 2] = goodsName;
sheetNew.Cells[7, 2] = goodsUnit;
sheetNew.Cells[7, 4] = goodsDepository;
pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
//sheetNew.Cells[12, 4] = pageFooter;
Console.WriteLine(pageFooter);
}
catch
{
errorCount++;
}
index++;
if (index > end)
{ break; }
//一页两个
try
{
goodsSN = sheetGoods.Cells[index, 1];
goodsID = sheetGoods.Cells[index, 2];
goodsName = sheetGoods.Cells[index, 3];
goodsUnit = sheetGoods.Cells[index, 4];
goodsDepository = sheetGoods.Cells[index, 5];
sheetNew.Cells[19, 2] = goodsSN;
sheetNew.Cells[20, 2] = goodsID;
sheetNew.Cells[21, 2] = goodsName;
sheetNew.Cells[22, 2] = goodsUnit;
sheetNew.Cells[22, 4] = goodsDepository;
pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
//sheetNew.Cells[12, 4] = pageFooter;
Console.WriteLine(pageFooter);
}
catch
{
errorCount++;
}
index++;
}
//删除模版页
sheetTemplate.Delete();
//更新数据后另存为新文件
workBookTemplate.SaveAs(saveFileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing,
missing, missing, missing, missing);
Console.WriteLine("All done.");
DateTime endTime = DateTime.Now;
Console.Write("{0}~{1},total time: {2}", startTime, endTime, endTime - startTime);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
workBookTemplate.Close(false, missing, missing);
workBookTemplate = null;
ThisApplication.Quit();
ThisApplication = null;
}
try
{
////打开刚才生成的Excel文件
//Microsoft.Office.Interop.Excel.Application NewApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Microsoft.Office.Interop.Excel.Workbook NewWorkBook;
//NewWorkBook = NewApplication.Workbooks.Open(strSaveFileName, missing, missing, missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing);
//Microsoft.Office.Interop.Excel.Worksheet NewSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets[1];
//NewApplication.Visible = true;
System.Diagnostics.Process.Start(saveFileName);//来打开新文件
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.Read();
}
}
--------
--- 开源的BUG管理系统,易于安装,使用方便!
http://sourceforge.net/projects/bugonline
基于Asp.net2.0,Ajax ,sqlserver2005,欢迎加入共同维护.
我本就乐于助人,现又不禁怜香惜玉起来,正要满口答应的时候,mm又说:"老大说了,这会给报酬的哦!",我喜出望外,然后商定完成后给我500大圆。ok,我在网上Ctrl+c,Ctrl +v了一通,做成了下面这个值500大圆的代码。
class Program
{
static void Main(string[] args)
{
PrintFilledCard();
}
private static void PrintFilledCard()
{
Console.Write("开始号:");
string stmp = Console.ReadLine();
int start = int.Parse(stmp);
Console.Write("结束号:");
stmp = Console.ReadLine();
int end = int.Parse(stmp);
Console.WriteLine("Waitting...");
string cardFileName = @"D:\Code\ExcelAuto\Cards.xls";
string goodsFileName = @"D:\Code\ExcelAuto\Goods.xls";
string saveFileName = @"D:\Code\ExcelAuto\Cards1.xls";
object missing = System.Reflection.Missing.Value;
Excel.Application ThisApplication = new ApplicationClass();
Workbook workBookTemplate = null;
Workbook workBookGoods = null;
try
{
//加载产品文件
workBookGoods = ThisApplication.Workbooks.Open(goodsFileName, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Worksheet sheetGoods = (Worksheet)workBookGoods.Sheets[1];
//加载Excel模板文件
workBookTemplate = ThisApplication.Workbooks.Open(cardFileName, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Worksheet sheetTemplate = (Worksheet)workBookTemplate.Sheets[1];
ThisApplication.Visible = false;
int index = start + 1;
int errorCount = 0;
int totalCount = sheetGoods.UsedRange.Rows.Count - 1;
// int totalCount = (int)numericTotal.Value;
end = end + 1;
object goodsSN = null;
object goodsID = null;
object goodsName = null;
object goodsDepository = null;
object goodsUnit = null;
string pageFooter;
DateTime startTime = DateTime.Now;
while (index <= end && errorCount < 3)
{
sheetTemplate.Copy(missing, workBookTemplate.Sheets[workBookTemplate.Sheets.Count]);
Worksheet sheetNew = (Worksheet)workBookTemplate.Sheets[workBookTemplate.Sheets.Count];
sheetNew.Name = (index - 1).ToString();
try
{
goodsSN = sheetGoods.Cells[index, 1];
goodsID = sheetGoods.Cells[index, 2];
goodsName = sheetGoods.Cells[index, 3];
goodsUnit = sheetGoods.Cells[index, 4];
goodsDepository = sheetGoods.Cells[index, 5];
sheetNew.Cells[4, 2] = goodsSN;
sheetNew.Cells[5, 2] = goodsID;
sheetNew.Cells[6, 2] = goodsName;
sheetNew.Cells[7, 2] = goodsUnit;
sheetNew.Cells[7, 4] = goodsDepository;
pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
//sheetNew.Cells[12, 4] = pageFooter;
Console.WriteLine(pageFooter);
}
catch
{
errorCount++;
}
index++;
if (index > end)
{ break; }
//一页两个
try
{
goodsSN = sheetGoods.Cells[index, 1];
goodsID = sheetGoods.Cells[index, 2];
goodsName = sheetGoods.Cells[index, 3];
goodsUnit = sheetGoods.Cells[index, 4];
goodsDepository = sheetGoods.Cells[index, 5];
sheetNew.Cells[19, 2] = goodsSN;
sheetNew.Cells[20, 2] = goodsID;
sheetNew.Cells[21, 2] = goodsName;
sheetNew.Cells[22, 2] = goodsUnit;
sheetNew.Cells[22, 4] = goodsDepository;
pageFooter = string.Format("第{0}页/共{1}页", index - 1, totalCount);
//sheetNew.Cells[12, 4] = pageFooter;
Console.WriteLine(pageFooter);
}
catch
{
errorCount++;
}
index++;
}
//删除模版页
sheetTemplate.Delete();
//更新数据后另存为新文件
workBookTemplate.SaveAs(saveFileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing,
missing, missing, missing, missing);
Console.WriteLine("All done.");
DateTime endTime = DateTime.Now;
Console.Write("{0}~{1},total time: {2}", startTime, endTime, endTime - startTime);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
workBookTemplate.Close(false, missing, missing);
workBookTemplate = null;
ThisApplication.Quit();
ThisApplication = null;
}
try
{
////打开刚才生成的Excel文件
//Microsoft.Office.Interop.Excel.Application NewApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Microsoft.Office.Interop.Excel.Workbook NewWorkBook;
//NewWorkBook = NewApplication.Workbooks.Open(strSaveFileName, missing, missing, missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing);
//Microsoft.Office.Interop.Excel.Worksheet NewSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets[1];
//NewApplication.Visible = true;
System.Diagnostics.Process.Start(saveFileName);//来打开新文件
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.Read();
}
}
--------
--- 开源的BUG管理系统,易于安装,使用方便!
http://sourceforge.net/projects/bugonline
基于Asp.net2.0,Ajax ,sqlserver2005,欢迎加入共同维护.