private void button3_Click(object sender, EventArgs e)
{
try
{
dataGridView1.DataSource = null;
dataGridView1.Columns.Clear();
string tablename = "ChuKu";
SqlConnection mConn = new SqlConnection(Conlist.ConStr);
SqlCommand cmd = new SqlCommand();
cmd.Connection = mConn;
cmd.CommandText = "select * from " + tablename + " where chukudate = '" + System.DateTime.Today.ToShortDateString() + "'";
cmd.CommandType = CommandType.Text;
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds, tablename);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.RowHeadersVisible = false;
//dataGridView1.ColumnHeadersVisible = false;
EXCELIO excel = new EXCELIO();
string path = @"D:\出库材料";
CodeSoft.MakePath(path);
string filename = @"D:\出库材料\" + System.DateTime.Today.ToShortDateString().Replace("/", "-") + "的材料出库" + ".xlsx";
bool ok = excel.ExportExcel(System.DateTime.Today.ToShortDateString().Replace("/", "-") + "材料出库明细", ds.Tables[0], filename);
if (ok == true)
{
MessageBox.Show("导出OK!!");
}
else
{
MessageBox.Show("导出 Fail !!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button2_Click(object sender, EventArgs e)
{
try
{
dataGridView1.DataSource = null;
dataGridView1.Columns.Clear();
SqlConnection mConn = new SqlConnection(Conlist.ConStr);
SqlCommand cmd = new SqlCommand();
cmd.Connection = mConn;
// MessageBox.Show(dateTimePicker1.Value.Date.ToShortDateString());
cmd.CommandText = "select * from " + tableName + " where rukudate between '" + dateTimePicker1.Value.Date + "' and '" + dateTimePicker2.Value.Date + "'";
// cmd.CommandText = "select NBSerial,NewNBSerial, Model ,ShipDate,Status from " + tablename + " where ShipDate = '" + dateTimePicker1.Value.Date +"'";
cmd.CommandType = CommandType.Text;
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds, tableName);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.RowHeadersVisible = false;
EXCELIO excel = new EXCELIO();
string path = @"D:\入库材料";
CodeSoft.MakePath(path);
string filename = dateTimePicker1.Value.Date.ToShortDateString().Replace("/", "-") + "到" + dateTimePicker2.Value.Date.ToShortDateString().Replace("/", "-") + "入库明细" + ".xlsx";
bool ok = excel.ExportExcel(dateTimePicker1.Value.Date.ToShortDateString() + "到" + dateTimePicker2.Value.Date.ToShortDateString() + "入库明细", ds.Tables[0], @"D:\入库材料\" + filename);
if (ok == true)
{
MessageBox.Show("导出OK!!");
}
else
{
MessageBox.Show("导出 Fail !!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.textBox1.Text == "Z")
{
MessageBox.Show("begin Port 001--199 OK !");
string pp = this.textBox1.Text.Trim();
string path = pp + ":\\MAC\\";
CodeSoft.MakePath(path);
path = pp + ":\\PORT\\";
CodeSoft.MakePath(path);
for (int i = 1; i < 200; i = i + 1)
{
path = pp + ":\\PORT\\" + "P" + i.ToString("000") + "\\";
CodeSoft.MakePath(path);
}
MessageBox.Show("Make Port 001--199 OK !");
}
}
public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
DataRow dr = dataTable.NewRow();
int columnNumber = dataTable.Columns.Count;
string[] hTxt = { "原机序号", "新机器序号", "型号", "出货日期", "状态" };
// dr.ItemArray = hTxt;
dataTable.Rows.InsertAt(dr, 1);
int rowNumber = dataTable.Rows.Count;//不包括字段名
int colIndex = 0;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");
return(false);
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = false;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = hTxt[colIndex - 1];
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
}
// 写入Excel
// MessageBox.Show(rowNumber.ToString() + " " + columnNumber.ToString ());
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber, columnNumber]];
range.NumberFormat = "@"; //设置单元格为文本格式
range.Value2 = objData;
range.EntireColumn.AutoFit(); //自动调整列宽
range = worksheet.Range[excel.Cells[1, 4], excel.Cells[rowNumber, 4]];
range.NumberFormat = @"yyyy-mm-dd";//
range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber, columnNumber]];
range.Value2 = objData;
worksheet.Name = fileName;
string path = @"D:\出货";
CodeSoft.MakePath(path);
string fileNameFinally = @"D:\出货\" + fileName + ".xlsx";
workbook.SaveAs(fileNameFinally, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
try
{
workbook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
excel.Quit();
}
if (isShowExcle)
{
System.Diagnostics.Process.Start(fileNameFinally);
}
return(true);
}